樹狀結構在關聯式資料表中的儲存與遞迴統計查詢實作

問題定義

資料表 Tree(ID, ParentID, Remark) 以遞迴關係表達階層結構。另一表 T(F1) 記錄節點引用。需求：依 Tree 父子關係，統計每個節點及其所有後代在 T 中的出現次數（含重複）。

範例資料

Tree:
ID  ParentID
1   0
2   1
3   1
4   2
5   4
6   5
7   2

T.F1 值序列：1,5,3,4,1,7,6,4,5,3,4,1,7,6,4

預期結果

解法：自訂函數遞迴取得祖先路徑

作者提出兩種 UDF 實作，核心皆為遞迴建構「祖先鏈」字串（如 1-2-4-5），再以 LIKE 或 CHARINDEX 比對統計。

方法一：UDF 遞迴（受限 32 層巢狀）

ALTER FUNCTION dbo.Get32Ancestors(@X INT)
RETURNS VARCHAR(250)
AS
BEGIN
    DECLARE @ID INT, @ReturnValue VARCHAR(250)
    SELECT TOP 1 @ID = ParentID FROM Tree WHERE ID = @X
    IF @ID <> @X
        SELECT @ReturnValue = CAST(ISNULL(dbo.Get32Ancestors(@ID), '') AS VARCHAR) + '-' + CAST(@X AS VARCHAR)
    ELSE
        SET @ReturnValue = CAST(@ID AS VARCHAR)
    RETURN @ReturnValue
END

方法二：維護層次資訊（效能最佳）

建議在 Tree 表新增 LevelPath 欄位（如 1/2/4/5/），觸發器維護。查詢時：

SELECT t.ID, COUNT(*) AS Counts
FROM Tree t
JOIN T ON T.F1 = t.ID OR T.F1 IN (SELECT ID FROM Tree WHERE LevelPath LIKE t.LevelPath + '%')
GROUP BY t.ID

或使用 CHARINDEX(t.LevelPath, child.LevelPath) = 1 判斷祖先關係，單一 SQL 完成統計，效能遠優於遞迴函數。

關鍵洞見

• ID/ParentID 僅適合儲存，不適合高頻查詢
• 主動維護「路徑編碼」（Materialized Path）可將階層查詢轉為範圍掃描
• T-SQL 遞迴受限 32 層，深層樹結構必須改用路徑編碼或 CTE（SQL 2005+）
• 遊標、暫存表、遞迴皆非必要；集合導向思維 + 預計算欄位才是關聯式資料庫的正解