下面是一个简单的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
