自底向上遞回求和（最低級別只有值）

我在 SQL Server 的產品層次結構中有一個基于樹的 SKU 結構。最低級別的 SKU 永遠只有值（這些是消耗值）。然后我想在每個級別生成層次結構的聚合。

這是示例表結構：

我需要一個查詢，該查詢將從 sku 級別 (IsSku=1) 開始，然后進行處理，將對 SKU 求和并進行產品類別級別的求和以獲得累計運行總數。

我見過幾個查詢，其中在分層結構中存在遞回求和，其中每個級別都已經有值，但我需要一個從具有值的最低級別開始并在向上移動時遞回計算總和的查詢。

我正在嘗試這些，但它們看起來主要是對分層資料求和，其中每個節點已經有一個值（在我的例子中是 Volume）。我需要從最低級別開始，并在我沿著層次結構向上時將聚合向上。我試圖用我的資料模擬這些帖子中的答案，但到目前為止我的資料設定沒有成功。

• 24394601
• 29127163
• 11408878

查詢的輸出應如下所示：

我從遞回 CTE 開始，它回傳層次結構可以聚合卷，如果該節點已經有卷，但似乎無法弄清楚如何從 SKU 級別開始并繼續聚合層次結構。

這是我的 CTE 的開始：

DECLARE @tblData TABLE
(
    [ID] INT NOT NULL,
    [ParentId] INT NULL,
    [Name] varchar(50) NOT NULL,
    [Volume] int NOT NULL,
    [IsSku] bit
)

INSERT INTO @tblData
VALUES 
 (1,-1,'All',0,0)
,(2,1,'Cat A',0,0)  
,(3,1,'Cat B',0,0)  
,(4,2,'Cat A.1',0,0)  
,(5,2,'Cat A.2',0,0)  
,(6,3,'Cat B.1',0,0)  
,(7,3,'Cat B.2',0,0)  
,(8,4,'SKU1',10,1)  
,(9,4,'SKU2',5,1)  
,(10,5,'SKU3',7,1)  
,(11,5,'SKU4',4,1)  
,(12,6,'SKU1',10,1)  
,(13,6,'SKU2',5,1)  
,(14,7,'SKU3',7,1)  
,(15,7,'SKU4',4,1)  

;WITH cte AS (   
    SELECT
        a.ID
        ,a.ParentID
        ,a.Name
        ,a.Volume
        ,CAST('/'   cast(ID as varchar)   '/' as varchar) Node
        ,0 AS level
        ,IsSku
    FROM @tblData AS a
    WHERE a.ParentID = -1

    UNION ALL

    SELECT
        b.ID
        ,b.ParentID
        ,b.Name
        ,b.Volume
        ,CAST(c.Node   CAST(b.ID as varchar)   '/' as varchar)
        ,level = c.level   1
        ,b.IsSku
    FROM @tblData AS b  
    INNER JOIN cte c
        ON b.ParentId = c.ID
)

SELECT c1.ID, c1.ParentID, c1.Name, c1.Node
    ,ISNULL(SUM(c2.Volume),0)
FROM cte c1
LEFT OUTER JOIN cte c2
    ON c1.Node <> c2.Node
    AND LEFT(c2.Node, LEN(c1.Node)) = c1.Node
GROUP BY c1.ID, c1.ParentID, c1.Name, c1.Node

任何幫助表示贊賞！

uj5u.com熱心網友回復：

這應該這樣做：

DECLARE @tbl TABLE(Id INT, ParentId INT, Name NVARCHAR(255), Volume INTEGER, IsSku BIT)
 
INSERT INTO @tbl
VALUES 
 (1,-1,'All',0,0)
,(2,1,'Cat A',0,0)  
,(3,1,'Cat B',0,0)  
,(4,2,'Cat A.1',0,0)  
,(5,2,'Cat A.2',0,0)  
,(6,3,'Cat B.1',0,0)  
,(7,3,'Cat B.2',0,0)  
,(8,4,'SKU1',10,1)  
,(9,4,'SKU2',5,1)  
,(10,5,'SKU3',7,1)  
,(11,5,'SKU4',4,1)  
,(12,6,'SKU1',10,1)  
,(13,6,'SKU2',5,1)  
,(14,7,'SKU3',7,1)  
,(15,7,'SKU4',4,1)  
SELECT * FROM @tbl
;

WITH cte AS (
    SELECT       
        Id,ParentId, Name, Volume, IsSku, CAST(Id AS VARCHAR(MAX)) AS Hierarchy
    FROM       
        @tbl
    WHERE ParentId=-1
    UNION ALL
    SELECT 
        t.Id,t.ParentId, t.Name, t.Volume, t.IsSku, CAST(c.Hierarchy   '|'   CAST(t.Id AS VARCHAR(MAX)) AS VARCHAR(MAX)) 
    FROM 
        cte c 
        INNER JOIN @tbl t
            ON c.Id = t.ParentId

)
SELECT Id,ParentId, Name, ChildVolume AS Volume, IsSku
FROM (
    SELECT c1.Id, c1.ParentId, c1.Name, c1. Volume, c1.IsSku, SUM(c2.Volume) AS ChildVolume
    FROM cte c1
        LEFT JOIN cte c2 ON c2.Hierarchy LIKE c1.Hierarchy   '%'
    GROUP BY c1.Id, c1.ParentId, c1.Name, c1. Volume, c1.IsSku
) x

基本上計算分三步進行：

1. 通過連接 Id 為每個后代遞回捕獲層次結構： CAST(c.Hierarchy '|' CAST(t.Id AS VARCHAR(MAX)) AS VARCHAR(MAX))

2. 將結果表與自身連接，以便每條記錄與自身及其所有后代連接： FROM cte c1 LEFT JOIN cte c2 ON c2.Hierarchy LIKE c1.Hierarchy '%'

3. 最后通過分組聚合每個層次結構的 Volume： SUM(c2.Volume) AS ChildVolume

這是參考 Ed Harper 對類似問題的回答：基于層次結構的聚合

uj5u.com熱心網友回復：

由于遞回 CTE 在 SQL Server 中的作業方式，很難讓這種邏輯有效地作業。它通常需要自連接整個結果集，或者使用 JSON 或 XML 之類的東西。

問題是，在 CTE 的每次遞回中，雖然看起來您正在處理整個集合，但實際上一次只反饋一行。因此，在遞回上不允許分組。

相反，最好用WHILE回圈簡單地遞回并插入臨時表或表變數，然后將其讀回聚合

使用OUTPUT子句查看中間結果

DECLARE @tmp TABLE (
  Id INTEGER,
  ParentId INTEGER,
  Name VARCHAR(7),
  Volume INTEGER,
  IsSku INTEGER,
  Level INT,
  INDEX ix CLUSTERED (Level, ParentId, Id)
);

INSERT INTO @tmp
  (Id, ParentId, Name, Volume, IsSku, Level)
-- OUTPUT inserted.Id, inserted.ParentId, inserted.Name, inserted.Volume, inserted.IsSku, inserted.Level
SELECT
  p.Id,
  p.ParentId,
  p.Name,
  p.Volume,
  p.IsSku,
  1
FROM Product p
WHERE p.IsSku = 1;

DECLARE @level int = 1;
WHILE (1=1)
BEGIN
    INSERT INTO @tmp
      (Id, ParentId, Name, Volume, IsSku, Level)
    -- OUTPUT inserted.Id, inserted.ParentId, inserted.Name, inserted.Volume, inserted.IsSku, inserted.Level
    SELECT
      p.Id,
      p.ParentId,
      p.Name,
      t.Volume,
      p.IsSku,
      @level   1
    FROM (
        SELECT
          t.ParentID,
          Volume = SUM(t.Volume)
        FROM @tmp t
        WHERE t.Level = @level
        GROUP BY
          t.ParentID
    ) t
    JOIN Product p ON p.Id = t.ParentID;

    IF (@@ROWCOUNT = 0)
        BREAK;
        
    SET @level  = 1;
END;

SELECT *
FROM @tmp
ORDER BY Id;

資料庫<>小提琴

由于萬圣節保護（在我的情況下，我看到了“不必要的”排序），此解決方案確實涉及阻塞運算子。您可以通過使用Itzik Ben-Gan 的分而治之方法，利用兩個表變數并在它們之間進行觸發器來避免它。

標籤：sql sql-server 查询语句 公用表表达式 递归查询

上一篇：這個SQL陳述句的意圖是什么？

下一篇：SQL-LEAD和LAG查詢