使用SQLSERVER 2005/2008 遞歸CTE查詢樹型結構的方法

下面是一個簡單的Family Tree 示例

　　復制代碼 代碼如下:

　　DECLARE @TT TABLE (ID intRelation varchar()Name varchar()ParentID int)
INSERT @TT SELECT Great GrandFather Thomas Bishop null UNION ALL
SELECT Grand Mom Elian Thomas Wilson UNION ALL
SELECT Dad James Wilson UNION ALL
SELECT Uncle Michael Wilson UNION ALL
SELECT Aunt Nancy Manor UNION ALL
SELECT Grand Uncle Michael Bishop UNION ALL
SELECT Brother David James Wilson UNION ALL
SELECT Sister Michelle Clark UNION ALL
SELECT Brother Robert James Wilson UNION ALL
SELECT Me Steve James Wilson

Query
;WITH FamilyTree
AS(
SELECT * CAST(NULL AS VARCHAR()) AS ParentName AS Generation FROM @TT
WHERE ParentID IS NULL
UNION ALL
SELECT Fam*FamilyTreeName AS ParentName Generation + FROM @TT AS Fam
INNER JOIN FamilyTree ON FamParentID = FamilyTreeID
)SELECT * FROM FamilyTree

Output:

　　
希望對您有幫助

Author: Petter Liu

From:http://tw.wingwit.com/Article/program/SQLServer/201404/30568.html