樹狀的資料結構在日常的程式設計當中挺常見的，舉凡像是留言板、人事組織、族譜、…等，都用得上，但當我們把樹狀結構的資料存進 SQL Server 之後想要撈出來，除了用跑迴圈之外大概就是用遞迴了，迴圈的寫法就不多介紹，本篇文章要來跟大家分享遞迴的寫法。
CTE 遞迴
CTE（Common Table Expression）顧名思義，它本質上是一個運算式，而這個運算式的結果是一個資料表，跟 View 很像，但還是有一些差別，其中一個差別就是 CTE 裡面可以包含指向自己的參考，這個特性讓 CTE 可以做遞迴運算。
我的實驗環境是我有一個 Genealogy 資料表，欄位有 Id、Name、ParentId，用來存放一個家族的簡易族譜。
目前資料表中有 Johnny 及 Mark 兩個家族的資料，兩個家族的關係樹狀結構如下圖：
接下來我們利用兩個範例，來展示 CTE 遞迴的寫法。
撈出 Johnny 家族的所有成員
DECLARE @ParentId INT = 1
;WITH Family
AS
(SELECT
*
,1 AS [Generation]
FROM Genealogy g WITH (NOLOCK)
WHERE g.Id = @ParentId
UNION ALL
SELECT
g1.*
,f.[Generation] + 1 AS [Level]
FROM Genealogy g1 WITH (NOLOCK)
INNER JOIN Family f
ON g1.ParentId = f.Id)
SELECT
*
FROM Family f
基本的運作邏輯是這樣的，先找出最上層的資料，再拿這個最上層資料混進查詢裡面找下一層資料，直到沒有下一層資料為止，如上圖中的範例，先找到祖先是誰，再拿祖先往下去找子孫，而這過程中產生的結果用 UNION ALL 連結起來回傳，就是這樣而已。
而且，這個過程中我們可以自己額外加工想要呈現的資料，比如我就加了一個 Generation 來表示是第幾代的成員。
撈出 Mark 家族第二代及第三代的成員
接下來，我們就來延伸一下 Generation 欄位的用法，如果我們要取得 Mark 家族中第二代及第三代的成員，這時候 Generation 就派上用場了，指定撈出 Generation 介於 2~3 之間的資料就是結果了。
以上，這個 CTE 遞迴應用在族譜上的寫法就分享給各位朋友，各位朋友如果還有其他 CTE 遞迴的應用，歡迎不吝分享。
