測試資料：家族關係表
DECLARE @TT TABLE (Id INT, Relation VARCHAR(25), Name VARCHAR(25), ParentId INT);
INSERT @TT VALUES
(1, 'Great GrandFather', 'Thomas Bishop', NULL),
(2, 'Grand Mom', 'Elian Thomas Wilson', 1),
(3, 'Dad', 'James Wilson', 2),
(4, 'Uncle', 'Michael Wilson', 2),
(5, 'Aunt', 'Nancy Manor', 2),
(6, 'Grand Uncle', 'Michael Bishop', 1),
(7, 'Brother', 'David James Wilson', 3),
(8, 'Sister', 'Michelle Clark', 3),
(9, 'Brother', 'Robert James Wilson', 3),
(10, 'Me', 'Steve James Wilson', 3);
遞迴 CTE 查詢
;WITH FamilyTree AS (
-- Anchor: 根節點
SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 0 AS Generation
FROM @TT WHERE ParentId IS NULL
UNION ALL
-- Recursive: 子節點
SELECT f.*, ft.Name AS ParentName, ft.Generation + 1
FROM @TT f INNER JOIN FamilyTree ft ON f.ParentId = ft.Id
)
SELECT * FROM FamilyTree;
輸出欄位說明
| 欄位 | 意義 |
|---|
| Id | 節點唯一識別 |
| Relation | 稱謂 |
| Name | 姓名 |
| ParentId | 父節點 Id |
| ParentName | 父節點姓名 (CTE 產生) |
| Generation | 代數 (根為 0) |
應用場景
- 組織架構 (員工-主管)。
- 產品分類無限層級。
- BOM 物料清單展開。
- 論壇回覆串聯。
效能提示
- 確保
ParentId 有索引。
- 加上
OPTION (MAXRECURSION 1000) 防無限遞迴。
- 大量資料考慮改用
HierarchyId (SQL 2008+) 或路徑編碼法。
