[小菜一碟] 利用 SQL Server 的 CTE(Common Table Expression)遞迴取得族譜中某人的所有子孫

樹狀的資料結構在日常的程式設計當中挺常見的,舉凡像是留言板人事組織族譜、…等,都用得上,但當我們把樹狀結構的資料存進 SQL Server 之後想要撈出來,除了用跑迴圈之外大概就是用遞迴了,迴圈的寫法就不多介紹,本篇文章要來跟大家分享遞迴的寫法。

CTE 遞迴

CTE(Common Table Expression)顧名思義,它本質上是一個運算式,而這個運算式的結果是一個資料表,跟 View 很像,但還是有一些差別,其中一個差別就是 CTE 裡面可以包含指向自己的參考,這個特性讓 CTE 可以做遞迴運算。

我的實驗環境是我有一個 Genealogy 資料表,欄位有 IdNameParentId,用來存放一個家族的簡易族譜。

目前資料表中有 JohnnyMark 兩個家族的資料,兩個家族的關係樹狀結構如下圖:

接下來我們利用兩個範例,來展示 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 遞迴的應用,歡迎不吝分享。

參考資料

C# 指南ASP.NET 教學ASP.NET MVC 指引
Azure SQL Database 教學SQL Server 教學Xamarin.Forms 教學