这个查询在SQL Server中创建逗号分隔列表的作用是什么？

Question

这个查询在SQL Server中创建逗号分隔列表的作用是什么？

sqlsql-serversql-server-2008

12

我用谷歌的帮助编写了这个查询，以创建一个包含表中数据的分隔列表，但是我并没有理解这个查询中发生的事情。

有人能解释一下发生了什么吗？

 SELECT 
    E1.deptno, 
    allemp = Replace ((SELECT E2.ename AS 'data()' 
                       FROM emp AS e2 
                       WHERE e1.deptno = e2.DEPTNO 
                       FOR xml PATH('')), ' ', ', ') 
 FROM EMP AS e1 
 GROUP BY DEPTNO;

给出了结果。

10  CLARK, KING, MILLER
20  SMITH, JONES, SCOTT, ADAMS, FORD
30  ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES

- Registered User

1

请注意，如果文本中包含像> < &这样的字符，您的代码将失败。您将获得字符扩展，如<，>和&。有一种更好的方法来进行连接，请参阅：https://dev59.com/Q2445IYBdhLWcg3wH2rH#5031297 - KM.

4个回答

6

一步一步地拆开它 - 从内到外。

步骤1：

运行最内层的查询并查看其输出：

SELECT E2.ename AS 'data()' 
FROM emp AS e2 
WHERE e2.DEPTNO = 10
FOR XML PATH('')

您应该得到类似于以下的输出：

CLARK KING MILLER

步骤2： REPLACE只是将空格替换为,，从而将您的输出转换为：

CLARK, KING, MILLER

步骤3： 外部查询获取deptno的值并结合内部查询结果生成最终结果。

- marc_s

1

SQL Server 2017通过new STRING_AGG函数使此过程变得更加容易。最近我看到了这篇文章，将我的STUFF/FOR XML策略改为使用新的字符串函数。这样可以避免额外的JOIN/SUBQUERY和FOR XML的开销（以及奇怪的编码问题）以及难以解释的SQL语句。

SELECT  E1.deptno, 
        STRING_AGG(E1.ename, ', ') AS allemp
FROM    EMP AS e1 
GROUP BY DEPTNO;

注意：还要确保查看对应的STRING_SPLIT，以使处理 SQL 分隔数据更加容易。

- SliverNinja - MSFT

0

外部查询检索部门编号列表，然后针对每个部门编号运行子查询以返回属于该部门的所有名称。子查询使用FOR XML语句将输出格式化为单行逗号分隔列表。

- Miika L.

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- GarethD · Accepted Answer

最简单的解释方法是看看如何在实际的XML中使用FOR XML PATH。想象一个简单的表Employee：

EmployeeID      Name
1               John Smith
2               Jane Doe

你可以使用

SELECT  EmployeeID, Name
FROM    emp.Employee
FOR XML PATH ('Employee')

这将创建以下 XML

<Employee>
    <EmployeeID>1</EmployeeID>
    <Name>John Smith</Name>
</Employee>
<Employee>
    <EmployeeID>2</EmployeeID>
    <Name>Jane Doe</Name>
</Employee>

从 PATH 中删除 'Employee' 将移除外部 XML 标记，因此该查询语句为：

SELECT  Name
FROM    Employee
FOR XML PATH ('')

将会创建

    <Name>John Smith</Name>
    <Name>Jane Doe</Name>

你所做的不是最理想的方式，列名“data()”会导致SQL出错，因为它尝试创建一个非法标签的XML标记，因此会生成以下错误：

列名'Data（）'包含FOR XML所需的无效XML标识符；'（'（0x0028）是第一个有问题的字符。

相关子查询隐藏了此错误并只生成没有标记的XML：

SELECT  Name AS [Data()]
FROM    Employee
FOR XML PATH ('')

创建

John Smith Jane Doe

你将空格替换为逗号，这很容易理解...

如果我是你，我会稍微改写查询:

SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH('')
            ), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO;

如果没有列别名，则不会创建任何xml标签，将逗号添加到select查询中意味着名称中带有空格的任何名称都不会导致错误，STUFF将删除第一个逗号和空格。

补充说明

为了阐明KM在评论中所说的内容，因为这似乎吸引了更多的关注，正确转义XML字符的方法是使用.value，如下所示：

SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO;