SQL 樹狀結構資料處理：遞迴與層級資訊維護的實戰解析

問題背景

給定一個儲存樹狀結構的 Tree 表（ID, ParentID, Remark）與一個記錄交易的 T 表（F1），要求根據 Tree 的父子關係，統計 T 表中每個 F1 值的出現次數，且「祖先」節點的計數必須包含所有「後代」節點的計數。

測試資料

-- Tree 表
INSERT INTO Tree (ID, ParentID) VALUES
(1,0),(2,1),(3,1),(4,2),(5,4),(6,5),(7,2);

-- T 表
INSERT INTO T (F1) VALUES
(1),(5),(3),(4),(1),(7),(6),(4),(5),(3),(4),(1),(7),(6),(4);

預期結果

解決方案：自訂函數取得完整祖先路徑

核心思路是為每個節點計算其完整的祖先鏈（如 0-1-3），再利用 LIKE 比對判斷 T 表中的 F1 是否屬於該節點的子樹。提供兩種函數實作：

方法一：遞迴 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
    SET @ReturnValue = CAST(ISNULL(dbo.Get32Ancestors(@ID), '') AS VARCHAR) + '-' + CAST(@X AS VARCHAR);
  ELSE
    SET @ReturnValue = CAST(@ID AS VARCHAR);
  RETURN @ReturnValue;
END

方法二：迴圈實作（無層數限制，效能較低）

ALTER FUNCTION GetAllAncestors(@X INT)
RETURNS VARCHAR(1000)
AS BEGIN
  DECLARE @ReturnValue VARCHAR(1000), @ID INT, @ParentID INT;
  SET @ID = -1;
  SELECT TOP 1 @ID = ISNULL(ID,0), @ParentID = ISNULL(ParentID,0) FROM Tree WHERE ID = @X;
  WHILE @ID <> @ParentID AND @ParentID <> 0 AND @ID > 0
    AND '-' + ISNULL(@ReturnValue,'') + '-' NOT LIKE '%-' + CAST(@ID AS VARCHAR) + '-%'
  BEGIN
    IF @ReturnValue IS NOT NULL SET @ReturnValue = '-' + @ReturnValue;
    SET @ReturnValue = CAST(@ID AS VARCHAR) + ISNULL(@ReturnValue,'');
    SET @ID = -1;
    SELECT TOP 1 @ID = ISNULL(ID,0), @ParentID = ISNULL(ParentID,0) FROM Tree WHERE ID = @ParentID;
  END
  SET @ReturnValue = '-' + @ReturnValue;
  IF @ID > 0 SET @ReturnValue = CAST(@ID AS VARCHAR) + ISNULL(@ReturnValue,'');
  IF @ParentID = 0 OR @ID = @ParentID SET @ReturnValue = '0-' + ISNULL(@ReturnValue,'');
  RETURN @ReturnValue;
END

最終統計查詢

SELECT 
  ID, 
  dbo.GetAllAncestors(ID) AS Ancestors,
  (SELECT COUNT(*) FROM T 
   WHERE '-' + dbo.GetAllAncestors(F1) + '-' LIKE '%-' + CAST(Tree.ID AS VARCHAR) + '-%') AS Cnt
FROM Tree;

心得與建議

• 遞迴雖是直覺作法，但 T-SQL 32 層限制易成瓶頸。
• 主動維護「層級資訊」（如路徑字串）可將查詢簡化為單一 SQL，效能大幅提升。
• 封裝程度視實際情況調整，不宜過度設計。