表:
| Id | Name |
|---|---|
| 1 | aaa |
| 1 | bbb |
| 1 | ccc |
| 1 | ddd |
| 1 | eee |
要求输出:
| Id | abc |
|---|---|
| 1 | aaa,bbb,ccc,ddd,eee |
查询:
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
)
FROM temp1 GROUP BY id
该查询工作正常。但我只是需要解释一下它是如何工作的，或者是否有其他或简单的方法来做到这一点。
我有点搞不懂了。
当前回答
在for xml path中，如果我们定义任何像[for xml path(' envelope ')]这样的值，那么这些标记将被添加到每一行中:
<ENVLOPE>
</ENVLOPE>
其他回答
Declare @Temp As Table (Id Int,Name Varchar(100))
Insert Into @Temp values(1,'A'),(1,'B'),(1,'C'),(2,'D'),(2,'E'),(3,'F'),(3,'G'),(3,'H'),(4,'I'),(5,'J'),(5,'K')
Select X.ID,
stuff((Select ','+ Z.Name from @Temp Z Where X.Id =Z.Id For XML Path('')),1,1,'')
from @Temp X
Group by X.ID
下面是它的工作原理:
1. 使用FOR XML获取XML元素字符串
在查询的末尾添加FOR XML PATH允许您将查询结果作为XML元素输出，元素名称包含在PATH参数中。例如，如果我们要运行以下语句:
SELECT ',' + name
FROM temp1
FOR XML PATH ('')
通过传入一个空字符串(FOR XML PATH("))，我们得到以下结果:
,aaa,bbb,ccc,ddd,eee
2. 用STUFF去掉前导逗号
STUFF语句字面上“塞入”一个字符串到另一个字符串，替换第一个字符串中的字符。但是，我们只是使用它来删除结果值列表的第一个字符。
SELECT abc = STUFF((
SELECT ',' + NAME
FROM temp1
FOR XML PATH('')
), 1, 1, '')
FROM temp1
STUFF的参数为:
要“填充”的字符串(在我们的例子中，是完整的名称列表 领先的逗号) 开始删除和插入字符的位置(1，我们正在填充到一个空白字符串中) 要删除的字符数(1，是前导逗号)
所以我们最终得到:
aaa,bbb,ccc,ddd,eee
3.加入id以获得完整列表
接下来，我们只需将其连接到临时表中的id列表中，以获得带有name的id列表:
SELECT ID, abc = STUFF(
(SELECT ',' + name
FROM temp1 t1
WHERE t1.id = t2.id
FOR XML PATH (''))
, 1, 1, '') from temp1 t2
group by id;
我们得到了结果:
| Id | Name |
|---|---|
| 1 | aaa,bbb,ccc,ddd,eee |
在for xml path中，如果我们定义任何像[for xml path(' envelope ')]这样的值，那么这些标记将被添加到每一行中:
<ENVLOPE>
</ENVLOPE>
Azure SQL数据库和SQL Server(从2017年开始)中有非常新的功能来处理这种确切的场景。我相信这可以作为您试图用XML/STUFF方法完成的工作的本地官方方法。例子:
select id, STRING_AGG(name, ',') as abc
from temp1
group by id
STRING_AGG - https://msdn.microsoft.com/en-us/library/mt790580.aspx
编辑:当我最初发布这篇文章时，我提到了SQL Server 2016，因为我认为我在一个潜在的功能上看到了它。要么是我记错了，要么是发生了什么变化，感谢修改版本的建议编辑。此外，我印象非常深刻，并没有完全意识到让我做出最终选择的多步骤审查过程。
我做了调试，最终返回我的'填充'查询到它的正常方式。
简单的
select * from myTable for xml path('myTable')
给出了要从我调试的触发器写入日志表的表的内容。
