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

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类型的主体。现在，我想创建一个查询，返回每个帖子的所有帖子和子帖子的数量。我用聚合函数来做这个。当我使用聚合函数时，我需要使用一个组。当我在group by中使用字段时，会得到以下异常:

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

XML数据类型不能进行比较或排序，除非使用IS NULL操作符。

How can I solve this?

我怎么解决这个问题?

My query is:

我查询的方法是:

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]

4 个解决方案

#1

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

你可以在CTE中进行聚合，然后加入它

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

#2

Can you do the Group by without selecting out the XML field, in a subquery, then select out the XML field, and the other collumns joining on your subquery?

在子查询中，您是否可以不选择XML字段，然后选择XML字段，以及在子查询中连接的其他集合来进行分组?

Using your query you'd get: e.g.

用你的问句你会得到:

SELECT Post.[Body], sq.* FROM 
(
    SELECT Post.PostId, Count(Children.PostId)
    FROM  
      dbo.Post Post, 
      dbo.Post Children 
    WHERE
      Children.ParentId = Post.PostId
    GROUP BY
      Post.PostId
) as sq
INNER JOIN Post on Post.PostId= sq.PostId

However you are using a cross join / ansi join here, which is not the best way to do it. A better way would be:

但是，您使用的是交叉连接/ ansi连接，这不是最好的方法。更好的办法是:

SELECT Post.[Body], sq.* FROM 
(
    SELECT Post.PostId, Count(Children.PostId)
    FROM  
      dbo.Post Post LEFT OUTER JOIN Children on Children.ParentId = Post.PostId
    GROUP BY
      Post.PostId
) as sq
INNER JOIN Post on Post.PostId= sq.PostId

#3

try this way hope it will help you

试试这个方法，希望对你有帮助

SELECT  Post.PostId, Post.[Body], Count(Children.PostId) over() as totalids
    FROM  
        dbo.Post Post, 
        dbo.Post Children 
    WHERE
        Children.ParentId = Post.PostId

as i have worked check this

正如我所做的，检查一下这个

 create table  xmltable  (attr1 int identity ,attr2 varchar(50),attr3  xml)

    insert into xmltable select 'i',(SELECT ( SELECT 'sometext' FOR XML PATH('body'),TYPE)FOR XML PATH(''),ROOT('SampleXML')) 
    union all
     select 'j',(SELECT ( SELECT 'sometext' FOR XML PATH('body'),TYPE)FOR XML PATH(''),ROOT('SampleXML'))
     union all
     select 'k',(SELECT ( SELECT 'sometext' FOR XML PATH('body'),TYPE)FOR XML PATH(''),ROOT('SampleXML'))
    union all
    select 'l',(SELECT ( SELECT 'sometext' FOR XML PATH('body'),TYPE)FOR XML PATH(''),ROOT('SampleXML'))


create table  xmlid  (x int ,y varchar(50))
  insert into xmlid select  1,'x'
    union all
     select 1,'y'
    union all
     select 2,'z'
    union all
     select 2,'xyz1'
     union all
     select 3,'xyz2'
    union all
     select 1,'xyz3'
     select * from  xmltable
    select * from  xmlid

create tables then

然后创建表

select * from  xmltable
 select * from  xmlid
 select  attr1,attr2, attr3,count(attr1) over(partition by attr2 ) as total_qtyover from  xmltable a,xmlid b
    where  a.attr1=b.x

also see this How to aggregrate without using `GROUP BY`?

还看到了如何不使用“GROUP BY”进行聚合吗?

#4

I'd break it down into two queries, one to get the IDs and child counts, and a separate one to get the bodies. Doing a GROUP BY requires the server to sort on the grouping fields, so you're potentially moving and comparing a lot of data when you don't need to (unless you have multiple post bodies with the same ID).

我将它分解为两个查询，一个查询id和子计数，另一个查询获取body。执行GROUP BY需要服务器对分组字段进行排序，所以在不需要时，您可能会移动和比较大量数据(除非您有多个具有相同ID的post主体)。

#1