我已经在google的帮助下编写了这个查询,以从表中创建一个分隔列表,但是我从这个查询中不了解任何内容.
任何人都可以解释我发生了什么
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工作.想象一下简单的表员工：
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标签,因此会产生以下错误：
Column name ‘Data()’ contains an invalid XML identifier as required by FOR XML; ‘(‘(0x0028) is the first character at fault.
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标签,并且在选择查询中添加逗号意味着任何具有空格的名称都不会导致错误,STUFF将删除第一个逗号和空格.
附录
要详细说明KM在评论中所说的话,因为这似乎还有更多的意见,正确的方式来排除XML字符将是使用.value如下：
SELECT E1.deptno,' + E2.ename
FROM emp AS e2
WHERE e1.deptno = e2.DEPTNO
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),'')
FROM EMP AS e1
GROUP BY DEPTNO;
