問題描述

我正在創(chuàng)建一個論壇，所以我創(chuàng)建了一個包含帖子的表格.其中一個字段是 Body，類型為 XML.現(xiàn)在我想創(chuàng)建一個查詢，返回所有帖子和每個帖子的子項數(shù)量.我正在使用聚合函數(shù)執(zhí)行此操作.當我使用聚合函數(shù)時，我需要使用一個組.當我使用 group by 中的字段時，會出現(xiàn)以下異常:

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 數(shù)據(jù)類型無法比較或排序，除非使用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 數(shù)據(jù)類型?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！