萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

樹狀結構在關聯表中的儲存與統計:遞迴與層次路徑解法

問題情境

給定一張樹狀結構表 Tree (ID, ParentID, Remark) 及一張記錄表 T (F1),需統計 T 表中每個 F1 值出現的次數,並將該次數「向上累加」至所有祖先節點。例如:節點 1 的計數包含自身、子節點 2、3、孫節點 4、7 以及更深層的所有後代在 T 表中的記錄總數。

測試資料

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

-- T 表 (F1 為 Tree.ID 的外鍵)
INSERT INTO T (F1) VALUES
(1),(5),(3),(4),(1),(7),(6),(4),(5),(3),(4),(1),(7),(6),(4);

預期結果

IDCounts
115
210
32
48
54
62
72

解法一:遞迴 UDF (受限 32 層)

函數 Get32Ancestors 遞迴向上尋找父節點,回傳路徑字串 (如 0-1-3)。

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

解法二:迴圈 UDF (無層數限制)

函數 GetAllAncestors 使用 WHILE 迴圈逐層向上,並偵測循環參考、斷碼、根節點等邊界情況,回傳統一格式的路徑字串 (以 - 分隔,首尾皆含分隔符,利於 LIKE 比對)。

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

統計查詢

利用路徑字串的 LIKE 比對,將 T 表記錄歸屬到所有祖先:

-- 使用無限層版本
SELECT
    ID,
    dbo.GetAllAncestors(ID) AS Path,
    (SELECT COUNT(*) FROM T
     WHERE '-' + dbo.GetAllAncestors(F1) + '-' LIKE '%-' + CAST(Tree.ID AS VARCHAR) + '-%') AS Cnt
FROM Tree

-- 使用 32 層版本 (相同邏輯)
SELECT
    ID,
    dbo.Get32Ancestors(ID) AS Path,
    (SELECT COUNT(*) FROM T
     WHERE '-' + dbo.Get32Ancestors(F1) + '-' LIKE '%-' + CAST(Tree.ID AS VARCHAR) + '-%') AS Cnt
FROM Tree

效能與架構建議

  • UDF 逐列執行效能較差,資料量大時建議改用「維護層次路徑欄位」 (如 HierarchyID 或自訂 Path 欄位) 並建立索引,查詢可簡化為單一 JOIN 或 LIKE '0-1-3-%'。
  • SQL Server 2005+ 可使用 CTE 遞迴查詢 (Common Table Expression) 替代 UDF,效能更佳且語法清晰。
  • 觸發器維護 Path 欄位時需注意並發與死鎖,適當鎖定順序 (由根向葉) 可降低風險。
copyright © 萬盛學電腦網 all rights reserved