資料結構
Id Name Parent
1 A 0
2 B 0
3 a1 1,2 -- 同時屬於 A、B
遞迴 CTE 解法
WITH cte AS (
SELECT * FROM tb WHERE Id = '1' -- 起始節點
UNION ALL
SELECT tb.* FROM tb
INNER JOIN cte ON CHARINDEX(CAST(cte.Id AS VARCHAR(10)), tb.Parent) > 0
)
SELECT * FROM cte;
原理解析
CHARINDEX(CAST(cte.Id AS VARCHAR), tb.Parent) > 0 判斷父欄位是否包含目前節點 Id。- 遞迴向下展開所有子孫節點。
- 注意：若資料量大、層級深，
CHARINDEX 掃描效能較差，建議正規化為關聯表。
改良版：防止循環引用
WITH cte AS (
SELECT *, CAST(',' + CAST(Id AS VARCHAR(10)) + ',' AS VARCHAR(MAX)) AS Path
FROM tb WHERE Id = '1'
UNION ALL
SELECT tb.*, cte.Path + CAST(tb.Id AS VARCHAR(10)) + ','
FROM tb INNER JOIN cte
ON CHARINDEX(',' + CAST(cte.Id AS VARCHAR(10)) + ',', ',' + tb.Parent + ',') > 0
WHERE cte.Path NOT LIKE '%,' + CAST(tb.Id AS VARCHAR(10)) + ',%'
)
SELECT * FROM cte;
