表:
| 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
该查询工作正常。但我只是需要解释一下它是如何工作的，或者是否有其他或简单的方法来做到这一点。
我有点搞不懂了。
当前回答
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，因为我认为我在一个潜在的功能上看到了它。要么是我记错了，要么是发生了什么变化，感谢修改版本的建议编辑。此外，我印象非常深刻，并没有完全意识到让我做出最终选择的多步骤审查过程。
其他回答
PATH模式用于从SELECT查询生成XML
1. SELECT
ID,
Name
FROM temp1
FOR XML PATH;
Ouput:
<row>
<ID>1</ID>
<Name>aaa</Name>
</row>
<row>
<ID>1</ID>
<Name>bbb</Name>
</row>
<row>
<ID>1</ID>
<Name>ccc</Name>
</row>
<row>
<ID>1</ID>
<Name>ddd</Name>
</row>
<row>
<ID>1</ID>
<Name>eee</Name>
</row>
Output是以元素为中心的XML，其中结果行集中的每个列值都包装在一个行元素中。因为SELECT子句没有为列名指定任何别名，所以生成的子元素名与SELECT子句中相应的列名相同。
为行集中的每一行添加一个标记。
2.
SELECT
ID,
Name
FROM temp1
FOR XML PATH('');
Ouput:
<ID>1</ID>
<Name>aaa</Name>
<ID>1</ID>
<Name>bbb</Name>
<ID>1</ID>
<Name>ccc</Name>
<ID>1</ID>
<Name>ddd</Name>
<ID>1</ID>
<Name>eee</Name>
对于第2步:如果指定的字符串长度为零，则不会生成包装元素。
3.
SELECT
Name
FROM temp1
FOR XML PATH('');
Ouput:
<Name>aaa</Name>
<Name>bbb</Name>
<Name>ccc</Name>
<Name>ddd</Name>
<Name>eee</Name>
4. SELECT
',' +Name
FROM temp1
FOR XML PATH('')
Ouput:
,aaa,bbb,ccc,ddd,eee
在步骤4中，我们将连接这些值。
5. SELECT ID,
abc = (SELECT
',' +Name
FROM temp1
FOR XML PATH('') )
FROM temp1
Ouput:
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
6. SELECT ID,
abc = (SELECT
',' +Name
FROM temp1
FOR XML PATH('') )
FROM temp1 GROUP by iD
Ouput:
ID abc
1 ,aaa,bbb,ccc,ddd,eee
在第6步中，我们根据ID对日期进行分组。
添加(source_string, start, length, add_string) 参数或参数 source_string 要修改的源字符串。 开始 在source_string中的位置删除长度字符，然后插入add_string。 长度 要从source_string中删除的字符数。 add_string 要在source_string的起始位置插入的字符序列。
SELECT ID,
abc =
STUFF (
(SELECT
',' +Name
FROM temp1
FOR XML PATH('')), 1, 1, ''
)
FROM temp1 GROUP by iD
Output:
-----------------------------------
| Id | Name |
|---------------------------------|
| 1 | aaa,bbb,ccc,ddd,eee |
-----------------------------------
我做了调试，最终返回我的'填充'查询到它的正常方式。
简单的
select * from myTable for xml path('myTable')
给出了要从我调试的触发器写入日志表的表的内容。
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，因为我认为我在一个潜在的功能上看到了它。要么是我记错了，要么是发生了什么变化，感谢修改版本的建议编辑。此外，我印象非常深刻，并没有完全意识到让我做出最终选择的多步骤审查过程。
下面是它的工作原理:
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 |
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
