Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE对于自定义分组，一向是比较烦琐的，幸好SQLServer2005提供了FOR XML PATH的语法能够很方便的解决该问题，同时采用自定义函数的方式还能够更方便的进行扩展；Oracle10g以后提供了一个wmsys.wm_concat函数，也可以起到类似的作用。

下面以SQLServer2005自带的AdventureWorks数据库中的2个表为例

HumanResources.Department --部门信息表

HumanResources.EmployeeDepartmentHistory --部门员工对照表

--使用自定义函数的方式

SELECT groupname,dbo.fn_concatemployees(departmentid) employeesconcat FROM HumanResources.Department

CREATE FUNCTION fn_concatemployees(@departmentid INT) RETURNS VARCHAR(1000)

AS

BEGIN

DECLARE @employeeidcancat AS VARCHAR(1000);

SET @employeeidcancat='';

SELECT @employeeidcancat=@employeeidcancat+CAST(employeeid AS VARCHAR(10))+';'

FROM HumanResources.EmployeeDepartmentHistory

WHERE departmentid=@departmentid;

RETURN @employeeidcancat;

END

--使用SQLServer2005的FOR XML PATH方式

SELECT groupname,

(SELECT CAST(o.employeeid AS VARCHAR(10))+';' AS [text()]

FROM HumanResources.EmployeeDepartmentHistory o

WHERE o.departmentid=c.departmentid

ORDER BY employeeid

FOR XML PATH('')) AS employeesconcat

FROM HumanResources.Department c

--在Oracle10g中采用wmsys.wm_concat方式

SELECT groupname,wmsys.wm_concat(employeeid) employeesconcat

FROM EmployeeDepartmentHistory

GROUP BY groupname;

ITPub博客

关于SQLServer2005的学习笔记——自定义分组的实现

最新文章