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;

關於SQLServer2005的學習筆記——自定義分組的實現

相關文章