为什么不是STUFF和FOR XML PATH不集中？

问题描述：

CREATE TABLE [dbo].[Things](
    [testid] [int] NOT NULL, 
    [testdesc] [varchar](10) NULL 
) ON [PRIMARY] 

CREATE TABLE [dbo].[ThingsStaging](
    [otherid] [int] NOT NULL, 
    [testid] [int] NOT NULL 
) ON [PRIMARY] 

INSERT INTO [dbo].[Things] ([testid], [testdesc]) VALUES (1, N'Stuff') 
INSERT INTO [dbo].[Things] ([testid], [testdesc]) VALUES (2, N'Things') 
INSERT INTO [dbo].[Things] ([testid], [testdesc]) VALUES (3, N'Orcs') 
INSERT INTO [dbo].[Things] ([testid], [testdesc]) VALUES (4, N'Grubs') 
INSERT INTO [dbo].[Things] ([testid], [testdesc]) VALUES (5, N'Shrooms') 

INSERT INTO [dbo].[ThingsStaging] ([otherid], [testid]) VALUES (1, 1) 
INSERT INTO [dbo].[ThingsStaging] ([otherid], [testid]) VALUES (1, 2) 
INSERT INTO [dbo].[ThingsStaging] ([otherid], [testid]) VALUES (1, 3) 
INSERT INTO [dbo].[ThingsStaging] ([otherid], [testid]) VALUES (2, 3) 
INSERT INTO [dbo].[ThingsStaging] ([otherid], [testid]) VALUES (2, 4) 

;with allThings(otherid, descs) 
as 
(
    select ts.otherid , 
    stuff ((select ', ' + blah.testdesc as [text()] 
      from (
       select distinct t.testdesc 
       from Things as t 
       where t.testid = ts.testid) as blah 
       for xml path('')), 1, 1, '') as stuffs 
    from ThingsStaging as ts 
) 
select * 
from allThings

现在运行此查询时，我得到

otherid stuffs 
1 Stuff 
1 Things 
1 Orcs 
2 Orcs 
2 Grubs

但我应该得到：

otherid  stuffs 
1  Stuff, Things, Orcs 
2  Orcs, Grubs

我不明白，我什么做错了。

答

我明白我做错了什么。代码会解释得更好。

select otherid, stuff((select ', ' + t.testdesc as [text()] 
         from Things as t 
         inner join ThingsStaging as its on t.testid = its.testid 
         where its.otherid = ts.otherid 
         for xml path('')), 1, 1, '') as descs 
from ThingsStaging as ts 
group by otherid

为什么不是STUFF和FOR XML PATH不集中？

相关推荐