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

对于自定义分组，一向是比较烦琐的，幸好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;

 注意：我个人认为尽量避免使用游标，因为它性能不高，个人推荐使用FOR XML PATH