給定一張樹狀結構表 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);
| ID | Counts |
|---|---|
| 1 | 15 |
| 2 | 10 |
| 3 | 2 |
| 4 | 8 |
| 5 | 4 |
| 6 | 2 |
| 7 | 2 |
函數 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
函數 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
HierarchyID 或自訂 Path 欄位) 並建立索引,查詢可簡化為單一 JOIN 或 LIKE '0-1-3-%'。CTE 遞迴查詢 (Common Table Expression) 替代 UDF,效能更佳且語法清晰。Path 欄位時需注意並發與死鎖,適當鎖定順序 (由根向葉) 可降低風險。