Início / dba / Perguntas / 203910
Accepted
crichavin
Asked: 2018-04-14 12:18:51 +0800 CST

Ajuda com CTE agregando crianças recursivamente

  • 772

Estou tentando criar uma consulta para calcular a quantidade "Disponível para Venda" de todos os itens no inventário. Nesse caso, um item pode ter estoque atual disponível, mas também pode ser um kit, que é composto de matérias-primas que podem ser montadas para formar esse bem acabado. Portanto, a quantidade disponível para venda é a quantidade atual disponível de Bens Acabados (FG) mais o mínimo dos componentes que podem ser feitos no FG.

Exemplo:

Digamos que estamos vendendo um kit de laptop composto por um laptop e uma bolsa de transporte. Se tivermos 2 kits já feitos e 6 laptops e 3 bolsas de transporte, nossa disponibilidade para venda desses kits é de 2 + 3 = 5 no total. As bolsas de transporte são o fator limitante neste caso. Apesar de termos 6 laptops, só podemos fazer mais 3 kits por causa das malas que nos limitam.

Cheguei até aqui e os cálculos funcionam do nível 2 mais baixo até o nível 1, mas o nível 0 não está correto. Então, neste caso, o cálculo para o kit de laptop está correto (11 em mãos + podemos fazer mais 4 = 15 disponíveis para vender). Mas o kit de laptop e bolsa de nível superior não está correto. O menor Avail to Sell dos filhos diretos do nível superior (Laptop & Bag Kit) é 15 + 3 desse kit está disponível = 18, não 14.

insira a descrição da imagem aqui

Estou pensando que talvez precise adicionar um segundo CTE recursivo em vez da junção esquerda que tenho na seleção final?

SQL Fiddle

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 respostas

  1. Best Answer

    Desenrole a hierarquia em uma tabela temporária primeiro (observe a coluna computada):

    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);

    Isso nos dá:

    ╔════╦════════╦══════════════════╦═══════════╦════ ═══╦══════════╦════════════╦═════════════╗
║ Id ║ MPath ║ DisplayText ║ OnHandQty ║ Level ║ ParentId ║ MaxCanMake ║ AvailToSell ║
╠════╬════════╬══════════════════╬═══════════╬════ ═══╬══════════╬════════════╬═════════════╣════════╣
║ 9 ║ 0 ║ Laptop e Kit de Bolsa ║ 3 ║ 0 ║ 0 ║ NULL ║ NULL ║
║ 26 ║ 0,20 ║ Bolsa ║ 23 ║ 1 ║ 9 ║ NULL ║ NULL ║
║ 8 ║ 0,5 ║ Kit de Laptop ║ 11 ║ 1 ║ 9 ║ NULL ║ NULL ║
║ 7 ║ 0.5.10 ║ Laptop ║ 5 ║ 2 ║ 8 ║ NULL ║ NULL ║
║ 6 ║ 0.5.15 ║ Fonte de alimentação ║ 4 ║ 2 ║ 8 ║ NULL ║ NULL ║
╚════╩════════╩══════════════════╩═══════════╩════ ═══╩══════════╩════════════╩═══════╝════════╝════════╝══════

    Agora calcule MaxCanMakepor nível, começando com o mais profundo:

    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;

    A coluna computada na tabela temporária reflete automaticamente a alteração em AvailToSell.

    A consulta de exibição final é então:

    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 ║
╠════════════════════╬═══════════╬════════════╬═══ ══════════╣
║ Kit de laptop e bolsa ║ 3 ║ 15 ║ 18 ║
║ --- Bolsa ║ 23 ║ 0 ║ 23 ║
║ ---Kit Notebook ║ 11 ║ 4 ║ 15 ║
║ ------Laptop ║ 5 ║ 0 ║ 5 ║
║ ------Fonte de alimentação ║ 4 ║ 0 ║ 4 ║
╚════════════════════╩═══════════╩════════════╩═══ ══════════╝

    dbfiddle

    • 4

relate perguntas