Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE關於解決樹形目錄是每種資料庫或大多數開發人員都要面對的問題，在這一點上Oracle走的跟前線一些，從9i起便提供了connect by進行了支援，10g又增強了相關語法；在SQLServer2005中，強大的CTE功能也提供了相應的解決方案，此外提供的表函式功能也給出了另外一種解決思路。

從功能上講的話，表函式方式更為靈活一些，畢竟基於過程的結構方式更容易實現負責的業務邏輯；但遞迴CTE構造起來更為清晰一些。

該文起源於《Microsoft SQL Server 2005技術內幕：T-SQL查詢》，但與文中所述不盡相同。

首先構建一個標準的樹形結構的員工表

CREATE TABLE Employees

(

EmpID INT,

MgrID INT,

EmpName VARCHAR(25),

Salary MONEY,

CHECK(EmpID<>MgrID)

);

GO

INSERT INTO Employees VALUES(1,NULL,'David',10000);

INSERT INTO Employees VALUES(2,1,'Eitan',7000);

INSERT INTO Employees VALUES(3,1,'Ina',7500);

INSERT INTO Employees VALUES(4,2,'Seraph',5000);

INSERT INTO Employees VALUES(5,2,'Jiru',5500);

INSERT INTO Employees VALUES(6,2,'Steve',4500);

INSERT INTO Employees VALUES(7,3,'Aaron',5000);

INSERT INTO Employees VALUES(8,5,'Lilach',3500);

INSERT INTO Employees VALUES(9,7,'Rita',3000);

INSERT INTO Employees VALUES(10,5,'Sean',3000);

INSERT INTO Employees VALUES(11,7,'Gabriel',3000);

INSERT INTO Employees VALUES(12,9,'Emilia',2000);

INSERT INTO Employees VALUES(13,9,'Michael',2000);

INSERT INTO Employees VALUES(14,9,'Didi',1500);

--讓我們先來看看Oracle是如何實現的吧

--獲取所有相關員工資訊，並構建其級別和相應的結構指向

SELECT EmpID,MgrID,EmpName,Salary,Level,sys_connect_by_path(NVL(EmpID,'0'),'->')

FROM Employees

CONNECT BY PRIOR EmpID=MgrID

START WITH MgrID IS NULL

--獲取員工的所有下級節點

SELECT EmpID,MgrID,EmpName,Salary,Level

FROM Employees

CONNECT BY PRIOR EmpID=MgrID

START WITH EmpID=9

--獲取員工的所有上級節點

SELECT EmpID,MgrID,EmpName,Salary,Level

FROM Employees

CONNECT BY PRIOR MgrID=EmpID

START WITH EmpID=14

--構建遞迴CTE，也可以靈活獲取滿足不同級別的上下級節點

WITH EmployeeTree

AS

(

SELECT EmpID,MgrID,EmpName,Salary,

0 AS Level,

CAST(CASE WHEN MgrID IS NULL THEN 'Root' END AS VARCHAR(50)) MgrList

FROM Employees

WHERE MgrID IS NULL --此處亦可修改為MgrID=@Root，即傳入的節點，即可得到想要的節點內容

UNION ALL

SELECT C.EmpID,C.MgrID,C.EmpName,C.Salary,

P.Level+1 AS Level,

CAST(CAST(P.MgrList AS VARCHAR(50))+'->'+CAST(C.EmpID AS VARCHAR(10)) AS VARCHAR(50)) MgrList

FROM EmployeeTree P,Employees C

WHERE C.MgrID=P.EmpID --AND P.Level<2 設定相關級別

)

--所有員工

SELECT * FROM EmployeeTree

--求某員工上級

SELECT * FROM EmployeeTree

WHERE CHARINDEX(MgrList,(SELECT MgrList FROM EmployeeTree WHERE EmpID=7))>0

--求某員工下級

SELECT * FROM EmployeeTree

WHERE MgrList LIKE (SELECT MgrList FROM EmployeeTree WHERE EmpID=7)+'%'

--求某員工下級並且符合相應級數的

SELECT * FROM EmployeeTree

WHERE MgrList LIKE (SELECT MgrList FROM EmployeeTree WHERE EmpID=7)+'%'

AND Level<=(SELECT Level FROM EmployeeTree WHERE EmpID=7)+1

--透過表函式方式返回相關節點

CREATE FUNCTION fn_GetEmployeeTree(@root AS INT)

RETURNS @Subs TABLE

(

EmpID INT,

Level INT

)

AS

BEGIN

DECLARE @Level AS INT;

SET @Level=0;

INSERT INTO @Subs(EmpID,Level) SELECT EmpID,@Level FROM Employees WHERE EmpID=@root;

WHILE @@rowcount>0

BEGIN

SET @Level=@Level+1;

INSERT INTO @Subs(EmpID,Level)

SELECT C.EmpID,@Level

FROM @SubS AS P

JOIN Employees AS C

ON P.Level=@Level-1

AND C.MgrID=P.EmpID

END

RETURN;

END

SELECT * FROM fn_GetEmployeeTree(1)

關於SQLServer2005的學習筆記——樹形結構

相關文章