此查询对创建逗号分隔列表 SQL Server 做了什么?

2022-01-31 00:00:00 sql sql-server-2008 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;

相关文章