如何在 GROUP BY 子句中添加 XML 数据类型?

2021-09-10 00:00:00 sql sql-server-2005 tsql sql-server

我正在创建一个论坛，所以我创建了一个包含帖子的表格.其中一个字段是 Body，类型为 XML.现在我想创建一个查询，返回所有帖子和每个帖子的子项数量.我正在使用聚合函数执行此操作.当我使用聚合函数时，我需要使用一个组.当我使用 group by 中的字段时，会出现以下异常:

I'm creating a forum, so I have created a table with posts. One of the fields is a Body with of the type XML. Now I would like to create a query that returns all the posts and the number of children of every post. I'm doing this with an aggregate function. I need to use a group by when I'm using aggregate function. When I use the field in the group by, I'll get the following exception:

XML 数据类型无法比较或排序，除非使用IS NULL 运算符.

The XML data type cannot be compared or sorted, except when using the IS NULL operator.

我该如何解决这个问题?

How can I solve this?

我的查询是:

SELECT 
    Post.PostId, Post.[Body], Count(Children.PostId)
FROM  
    dbo.Post Post, 
    dbo.Post Children 
WHERE
    Children.ParentId = Post.PostId
GROUP BY
    Post.PostId, 
    Post.[Body]

推荐答案

您可以在 CTE 中进行聚合，然后加入该聚合

You can do the aggregation in a CTE then join onto that

WITH Children(Cnt, ParentId)
     AS (SELECT COUNT(*),
                ParentId
         FROM   dbo.Post
         GROUP  BY ParentId)
SELECT P.PostId,
       P.[Body],
       ISNULL(Cnt, 0) AS Cnt
FROM   dbo.Post P
       LEFT JOIN Children /*To include childless posts*/
         ON Children.ParentId = P.PostId
ORDER  BY P.PostId

相关文章