此查询如何创建逗号分隔列表 SQL Server? [英] What this query does to create comma delimited list SQL Server?

问题描述

我在 google 的帮助下编写了此查询，以从表中创建一个分隔列表，但我对此查询一无所知.

I've written this query with the help of google to create a delimited list from a table but I didn't understand anything from this query.

谁能告诉我这是怎么回事

Can anyone explain me what's happening

 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

推荐答案

解释它的最简单方法是查看 FOR XML PATH 如何为实际的 XML 工作.想象一个简单的表 Employee:

The simplest way of explaining it is to look at how FOR XML PATH works for actual XML. Imagine a simple table Employee:

EmployeeID      Name
1               John Smith
2               Jane Doe

你可以使用

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

这将创建 XML 如下

This would create XML as follows

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

从 PATH 中删除 'Employee' 会删除外部 xml 标记，因此此查询:

Removing the 'Employee' from PATH removes the outer xml tags so this query:

SELECT  Name
FROM    Employee
FOR XML PATH ('')

会创造

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

然后你所做的并不理想，列名'data()'强制出现一个sql错误，因为它试图创建一个不是合法标签的xml标签，所以产生了以下错误:

What you are then doing is not ideal, the column name 'data()' forces an sql error because it is trying to create an xml tag which is not a legal tag, so the following error is generated:

列名Data()"包含 FOR XML 要求的无效 XML 标识符；'('(0x0028) 是第一个出错的字符.

Column name 'Data()' contains an invalid XML identifier as required by FOR XML; '('(0x0028) is the first character at fault.

相关子查询隐藏了这个错误，只生成没有标签的 XML:

The correlated subquery hides this error and just generates the XML with no tags:

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

创造

John Smith Jane Doe

然后你用逗号替换空格，相当不言自明...

You are then replacing spaces with commas, fairly self explanatory...

如果我是你，我会稍微调整一下查询:

If I were you I would adapt the query slightly:

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 将删除第一个逗号和空格.

Having no column alias will mean no xml tags are created, and adding the comma within the select query means any names with spaces in will not cause errors,STUFF will remove the first comma and space.

附录

为了详细说明 KM 在评论中所说的内容，因为这似乎获得了更多视图，转义 XML 字符的正确方法是使用 .value 如下:

To elaborate on what KM has said in a comment, as this seems to be getting a few more views, the correct way to escape XML characters would be to use .value as follows:

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; 

这篇关于此查询如何创建逗号分隔列表 SQL Server?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！