关于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;