问题描述
我正在创建一个论坛，所以我创建了一个包含帖子的表格.其中一个字段是 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
这篇关于如何在 GROUP BY 子句中添加 XML 数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！