主页 / dba / 问题 / 203910
Accepted
crichavin
Asked: 2018-04-14 12:18:51 +0800 CST

帮助 CTE 递归地聚合子级

  • 772

我正在尝试创建一个查询来计算库存中所有物品的“可出售”数量。在这种情况下，一个项目可以有当前可用的库存，但也可以是一个套件，它由可以组装成成品的原材料组成。因此，可供出售的是当前可用的成品 (FG) 数量加上可以制成 FG 的最少组件。

例子：

假设我们正在销售由笔记本电脑和手提包组成的笔记本电脑套件。如果我们已经制作了 2 个套件、6 台笔记本电脑和 3 个手提包，那么我们可以出售的这些套件是 2 + 3 = 5 个。在这种情况下，手提袋是限制因素。即使我们有 6 台笔记本电脑，我们也只能再制作 3 台套件，因为我们的包限制了我们。

我已经做到了这一点，计算工作从最低级别 2 到级别 1，但级别 0 不正确。所以在这种情况下，笔记本电脑套件的计算是正确的（手头有 11 个 + 我们可以再做 4 个 = 15 个可供出售）。但是顶级笔记本电脑和包套件是不正确的。顶层（笔记本电脑和包套件）的直系子代的最低可售率为 15 + 3 现有套件 = 18，而不是 14。

在此处输入图像描述

我在想也许我需要添加第二个递归 CTE 而不是我在最终选择中的左连接？

SQL小提琴

CREATE TABLE Item (
  Id INT,
  ParentId INT,
  DisplaySeq INT,  
  DisplayText VARCHAR(30),
  OnHandQty INT
  );

INSERT INTO Item (Id, ParentId, DisplaySeq, DisplayText, OnHandQty) VALUES
(9, NULL, 0, 'Laptop & Bag Kit', 3),
(8, 9, 5, 'Laptop Kit', 11),
(7, 8, 10, 'Laptop', 5),
(6, 8, 15, 'Power Supply', 4),
(26, 9, 20, 'Bag', 23)
;

;WITH items AS (
  SELECT 
    Id 
    , 0 as ParentId
    , Id as RootId
    , 0 AS Level
    , CAST(DisplaySeq AS VARCHAR(255)) AS Path
    , CAST('---' AS varchar(100)) AS LVL
    , CAST(DisplayText as VARCHAR(255)) as DisplayText
    , OnHandQty
  FROM Item 
  WHERE ParentId IS NULL

  UNION ALL

  SELECT 
    child.Id
    , child.ParentId
    , parent.RootId
    , Level + 1
    , CAST(parent.Path + '.' + CAST(child.DisplaySeq AS VARCHAR(255)) AS VARCHAR(255)) AS Path
    , CAST('---' + parent.LVL AS varchar(100)) AS LVL
    , CAST(parent.LVL + child.DisplayText as VARCHAR(255)) as DisplayText
    , child.OnHandQty
  FROM 
    Item child
      INNER JOIN items parent 
      ON parent.Id = child.ParentId
  )

SELECT 
  t.Path
  , t.RootId
  , t.Id
  , t.ParentId
  , t.Level
  , t.DisplayText
  , t.OnHandQty
  , COALESCE(s.MaxCanMake, t.OnHandQty) as MaxCanMake
  , t.OnHandQty + COALESCE(s.MaxCanMake, 0) as AvailToSell
FROM 
  items t
    left join (
        Select 
          ParentId,
          MIN(OnHandQty) as MaxCanMake
        FROM items
        GROUP BY ParentId
      ) as s
      ON t.Id = s.ParentId

ORDER BY t.Path
sql-server cte

1 个回答

  1. Best Answer

    首先将层次结构展开到临时表中（注意计算列）：

    CREATE TABLE #Items
(
    Id integer PRIMARY KEY,
    MPath varchar(255) NOT NULL,
    DisplayText varchar(30) NOT NULL,
    OnHandQty integer NOT NULL,
    [Level] integer NOT NULL,
    ParentId integer NOT NULL,
    MaxCanMake integer NULL,
    AvailToSell AS OnHandQty + MaxCanMake
);

WITH Items AS
(
    SELECT 
        I.Id, 
        MPath = CONVERT(varchar(255), I.DisplaySeq), 
        I.DisplayText, 
        I.OnHandQty, 
        0 AS [Level], 
        0 AS ParentId
    FROM dbo.Item AS I 
    WHERE I.ParentId IS NULL

    UNION ALL

    SELECT 
        I.Id, 
        CONVERT(varchar(255), Parent.MPath + '.' + CONVERT(varchar(11), I.DisplaySeq)),
        I.DisplayText, 
        I.OnHandQty, 
        Parent.[Level] + 1, 
        I.ParentId
    FROM Items AS Parent
    JOIN dbo.Item AS I WITH (FORCESEEK)
        ON I.ParentId = Parent.Id
)
INSERT #Items
    (Id, MPath, DisplayText, OnHandQty, [Level], ParentId)
SELECT
    I.Id, I.MPath, I.DisplayText, I.OnHandQty, I.[Level], I.ParentId
FROM Items AS I
OPTION (MAXRECURSION 0);

-- Useful index
CREATE INDEX i 
ON #Items (ParentId, AvailToSell);

    这给了我们：

    ╔════╦════════╦══════════════════╦═══════════╦════ ═══╦══════════╦════════════╦═════════════╕
║ Id ║ MPath ║ DisplayText ║ OnHandQty ║ Level ║ ParentId ║ MaxCanMake ║ AvailToSell ║
╠════╬════════╬══════════════════╬═══════════╬════ ═══╬══════════╬════════════╬═════════════╣
║ 9 ║ 0 ║ 笔记本电脑和包包 ║ 3 ║ 0 ║ 0 ║ NULL ║ NULL ║
║ 26 ║ 0.20 ║ 袋子 ║ 23 ║ 1 ║ 9 ║ 空║ 空║
║ 8 ║ 0.5 ║ 笔记本电脑套件 ║ 11 ║ 1 ║ 9 ║ NULL ║ NULL ║
║ 7 ║ 0.5.10 ║ 笔记本电脑 ║ 5 ║ 2 ║ 8 ║ NULL ║ NULL ║
║ 6 ║ 0.5.15 ║ 电源 ║ 4 ║ 2 ║ 8 ║ NULL ║ NULL ║
╚════╩════════╩══════════════════╩═══════════╩════ ═══╩══════════╩════════════╩═════════════╝

    现在计算MaxCanMake每个级别，从最深开始：

    DECLARE @Level integer =
(
    SELECT MAX(I.[Level])
    FROM #Items AS I
);

WHILE @Level >= 0
BEGIN
    UPDATE I
    SET I.MaxCanMake = 
        ISNULL
        (
            (
                SELECT TOP (1)
                    I2.AvailToSell
                FROM #Items AS I2
                WHERE I2.ParentId = I.Id
                ORDER BY
                    I2.AvailToSell ASC
            ),
            0
        )
    FROM #Items AS I
    WHERE I.[Level] = @Level;

    SET @Level -= 1;
END;

    临时表中的计算列会自动反映AvailToSell.

    最终的显示查询是：

    SELECT
    DisplayText = REPLICATE('---', I.[Level]) + I.DisplayText,
    I.OnHandQty,
    I.MaxCanMake,
    I.AvailToSell 
FROM #Items AS I
ORDER BY 
    I.MPath;

    ╔════════════════════╦═══════════╦════════════╦═══ ══════════╗
║ DisplayText ║ OnHandQty ║ MaxCanMake ║ AvailToSell ║
╠════════════════════╬═══════════╬════════════╬═══ ══════════╣
║ 笔记本电脑和包包 ║ 3 ║ 15 ║ 18 ║
║---袋子║23║0║23║
║ ---笔记本电脑套件 ║ 11 ║ 4 ║ 15 ║
║ ------笔记本电脑 ║ 5 ║ 0 ║ 5 ║
║ ------电源 ║ 4 ║ 0 ║ 4 ║
╚════════════════════╩═══════════╩════════════╩═══ ══════════╝

    小提琴手

    • 4

相关问题