欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

使用SQLSERVER 2005/2008 遞歸CTE查詢樹(shù)型結(jié)構(gòu)的方法

 更新時(shí)間：2011年10月31日 23:36:12   作者：  
我們經(jīng)常遇到樹(shù)型結(jié)構(gòu)，把它們顯示在一個(gè)類似TreeView控件上的情況。這時(shí)我們可以使用Recursive Common Table Expressions(CTE)實(shí)現(xiàn)
下面是一個(gè)簡(jiǎn)單的Family Tree 示例：
復(fù)制代碼 代碼如下:

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, 'Brother', '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

Output:

query_result
希望對(duì)您有幫助

Author: Petter Liu

相關(guān)文章

最新評(píng)論