使用SQLSERVER 2005/2008 递归CTE查询树型结构的方法
下面是一个简单的Family Tree 示例：
<div class="codetitle"><a style="CURSOR: pointer" data="33678" class="copybut" id="copybut33678" onclick="doCopy('code33678')"> 代码如下:
<div class="codebody" id="code33678">
DECLARE @TT TABLE (ID int,Relation varchar(25),Name varchar(25),ParentID int)
INSERT @TT SELECT 1,' Great GrandFather','Thomas Bishop',null UNION ALL
SELECT 2,'Grand Mom','Elian Thomas Wilson',1 UNION ALL
SELECT 3,'Dad','James Wilson',2 UNION ALL
SELECT 4,'Uncle','Michael Wilson',2 UNION ALL
SELECT 5,'Aunt','Nancy Manor',2 UNION ALL
SELECT 6,'Grand Uncle','Michael Bishop',1 UNION ALL
SELECT 7,'Brother','David James Wilson',3 UNION ALL
SELECT 8,'Sister','Michelle Clark',3 UNION ALL
SELECT 9,'Robert James Wilson',3 UNION ALL
SELECT 10,'Me','Steve James Wilson',3 ----------Query---------------------------------------
;WITH FamilyTree
AS(
SELECT
,CAST(NULL AS VARCHAR(25)) AS ParentName,0 AS Generation FROM @TT
WHERE ParentID IS NULL
UNION ALL
SELECT Fam.,FamilyTree.Name AS ParentName,Generation + 1 FROM @TT AS Fam
INNER JOIN FamilyTree ON Fam.ParentID = FamilyTree.ID
)SELECT * FROM FamilyTree
