資料庫設計中常常會遇到需要儲存樹形結構，比如員工關係表、組織結構表，等等。

點選(此處)摺疊或開啟

--測試資料
CREATE TABLE #Employees(
EmployeeCode varchar(20) NOT NULL PRIMARY KEY CLUSTERED,
ReportToCode varchar(20) NULL)
GO
INSERT INTO #Employees VALUES(\'A\',NULL)
INSERT INTO #Employees VALUES(\'B\',\'A\')
INSERT INTO #Employees VALUES(\'C\',\'A\')
INSERT INTO #Employees VALUES(\'D\',\'A\')
INSERT INTO #Employees VALUES(\'E\',\'B\')
INSERT INTO #Employees VALUES(\'F\',\'B\')
INSERT INTO #Employees VALUES(\'G\',\'C\')
INSERT INTO #Employees VALUES(\'H\',\'D\')
INSERT INTO #Employees VALUES(\'I\',\'D\')
INSERT INTO #Employees VALUES(\'J\',\'D\')
INSERT INTO #Employees VALUES(\'K\',\'J\')
INSERT INTO #Employees VALUES(\'L\',\'J\')
INSERT INTO #Employees VALUES(\'M\',\'J\')
INSERT INTO #Employees VALUES(\'N\',\'K\')
GO
/*
可能遇到的查詢問題：
1. 員工\'D\'的所有直接下屬
2. 員工\'D\'的所有2級以內的下屬（包括直接下屬和直接下屬的下屬）
3. 員工\'N\'的所有上級（按報告線順序列出）
4. 員工@EmployeeCode的所有@LevelDown級以內的下屬（@EmployeeCode和@LevelDown以變數傳入）
DECLARE @EmployeeCode varchar(20), @LevelDown int;
SET @EmployeeCode = \'D\';
SET @LevelDown = 2;
5. 員工@EmployeeCode的所有@LevelUp級以內的上級（@EmployeeCode和@LevelUp以變數傳入）
DECLARE @EmployeeCode varchar(20), @LevelUp int;
SET @EmployeeCode = \'N\';
SET @LevelUp = 2;
*/
--用遞迴CTE實現員工樹形關係表
WITH CTE AS(
SELECT
EmployeeCode,
ReportToCode,
ReportToDepth = 0,
ReportToPath = CAST(\'/\' + EmployeeCode + \'/\' AS varchar(200))
FROM #Employees
WHERE ReportToCode IS NULL
UNION ALL
SELECT
e.EmployeeCode,
e.ReportToCode,
ReportToDepth = mgr.ReportToDepth + 1,
ReportToPath = CAST(mgr.ReportToPath + e.EmployeeCode + \'/\' AS varchar(200))
FROM #Employees e
INNER JOIN CTE mgr
ON e.ReportToCode = mgr.EmployeeCode
)
SELECT * FROM CTE ORDER BY ReportToPath

樹形結構的儲存與查詢

相關文章