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