SQLServer 的樹狀結構寫法
參考網址
1. 建立樹狀結構範例 TABLE
CREATE TABLE tblDepartments(
DepartmentID int,
DepartmentName varchar(50),
ParentDepartmentID int,
constraint tblDepartments_P0 primary key (DepartmentID)
)
2. 建立測試資料
INSERT INTO tblDepartments(DepartmentID, DepartmentName, ParentDepartmentID) VALUES(1, 'HR', NULL);
INSERT INTO tblDepartments(DepartmentID, DepartmentName, ParentDepartmentID) VALUES(2, 'IT', NULL);
INSERT INTO tblDepartments(DepartmentID, DepartmentName, ParentDepartmentID) VALUES(3, 'Networking', 2);
INSERT INTO tblDepartments(DepartmentID, DepartmentName, ParentDepartmentID) VALUES(4, 'Development', 2);
INSERT INTO tblDepartments(DepartmentID, DepartmentName, ParentDepartmentID) VALUES(5, 'ASP.NET Development', 4);
INSERT INTO tblDepartments(DepartmentID, DepartmentName, ParentDepartmentID) VALUES(6, 'J2EE Development', 4);
INSERT INTO tblDepartments(DepartmentID, DepartmentName, ParentDepartmentID) VALUES(7, 'C#.NET', 5);
INSERT INTO tblDepartments(DepartmentID, DepartmentName, ParentDepartmentID) VALUES(8, 'VB.NET', 5);
3. 寫出樹狀結構 SQL (若 Branch 為 'Y' 表示是別人的父親(分支))
WITH Hierarchy(DepartmentID, DepartmentName, ParentDepartmentID, HLevel)
AS
(
SELECT DepartmentID
,DepartmentName
,ParentDepartmentID
,0 HLevel
FROM tblDepartments
WHERE ParentDepartmentID is Null
UNION ALL
SELECT SubDepartment.DepartmentID
,SubDepartment.DepartmentName
,SubDepartment.ParentDepartmentID
,HLevel + 1
FROM tblDepartments SubDepartment INNER JOIN Hierarchy ParentDepartment
ON SubDepartment.ParentDepartmentID = ParentDepartment.DepartmentID
)
SELECT M.DepartmentID
,DepartmentName = Replicate('.', M.HLevel) + M.DepartmentName
,CASE
WHEN D.ParentDepartmentID IS NULL THEN 'N'
ELSE 'Y'
END Branch
,M.HLevel
,M.ParentDepartmentID
FROM Hierarchy M Left Outer Join
(SELECT DISTINCT ParentDepartmentID FROM Hierarchy WHERE ParentDepartmentID is Not Null) D
ON M.DepartmentID = D.ParentDepartmentID
