問題描述
我正在創建一個論壇，所以我創建了一個包含帖子的表格.其中一個字段是 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 數據類型?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！