Início / dba / Perguntas / 159340
Accepted
McNets
Asked: 2016-12-29 06:43:34 +0800 CST

Como obter o último nível de uma lista hierárquica, agrupar por algum campo

  • 772

Dado o esquema:

create table containers (
    id int primary key, 
    name text, 
    product_id int, 
    lot int, 
    qty int, 
    parent_id int);

create table orders_items (
    id int,
    position int,
    container_id int);

Onde containersé uma tabela hierárquica com um número desconhecido de níveis.

containers.parent_id = containers.id OR null

Um contêiner pode conter outros contêineres, esta é uma estrutura de árvore de contêineres:

  23 Box 40 Parent  [ID: NULL, Lot: NULL, Qty: SUM(20+20)=40]
   |
   |--22 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
   |   |--21 Bag 10 [Parent ID: 22, Lot: 701, Qty: 10]
   |   |--22 Bag 10 [Parent ID: 22, Lot: 703, Qty: 10]
   | 
   |--19 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
       |--17 Bag 10 [Parent ID: 19, Lot: 700, Qty: 10]
       |--18 Bag 10 [Parent ID: 19, Lot: 701, Qty: 10]

Para nós um contentor é considerado como uma unidade expedidora, dependendo dos requisitos do cliente um contentor pode ser um simples saco de plástico de 10 peças, ou uma palete completa com 10 caixas de 5000 peças cada uma. Apenas os contêineres de último nível têm um lote atribuído, os contêineres pais podem conter lotes diferentes, mas sempre pertencentes ao mesmo produto.

Precisamos listar todos os produtos incluídos em um único pedido, agrupados por lote.

  • Produtos diferentes não podem ter o mesmo lote.
  • Um contêiner só pode conter contêineres do mesmo produto.

Dados de amostra:

insert into containers values
  (23, 'Box 40',  2, null, 40,  null)
, (16, 'Pallet',  1, null, 120, null)
, (12, 'Bag 20',  1, null, 20,  14)
, (13, 'Bag 20',  1, null, 20,  14)
, (14, 'Box 40',  1, null, 40,  16)
, (19, 'Bag 20',  2, null, 20,  23)
, (22, 'Bag 20',  2, null, 20,  23)
, (5,  'Bag 20',  1, null, 20,  7)
, (6,  'Bag 20',  1, null, 20,  7)
, (7,  'Box 40',  1, null, 40,  16)
, (1,  'Bag 10',  1, 500,  10,  5)
, (2,  'Bag 10',  1, 501,  10,  5)
, (3,  'Bag 10',  1, 502,  10,  6)
, (4,  'Bag 10',  1, 500,  10,  6)
, (8,  'Bag 10',  1, 600,  10,  12)
, (9,  'Bag 10',  1, 601,  10,  12)
, (10, 'Bag 10',  1, 502,  10,  13)
, (11, 'Bag 10',  1, 501,  10,  13)
, (15, 'Box 40',  1, 600,  40,  16)
, (17, 'Bag 10',  2, 700,  10,  19)
, (18, 'Bag 10',  2, 701,  10,  19)
, (20, 'Bag 10',  2, 703,  10,  22)
, (21, 'Bag 10',  2, 701,  10,  22);

insert into orders_items values
(1, 1, 16),
(1, 2, 23);

O número do pedido 1tem duas linhas de detalhes, contêineres 16e 23, preciso incluir todos os lotes nesses contêineres.

Neste exemplo, o resultado deve mostrar estas linhas:

+----+------------+-------------+------+------+------------+
| id |    name    |  product_id |  lot |  qty |  parent_id |
+----+------------+-------------+------+------+------------+
|  1 |  'Bag 10'  |           1 |  500 |   10 |          5 |
|  2 |  'Bag 10'  |           1 |  501 |   10 |          5 |
|  3 |  'Bag 10'  |           1 |  502 |   10 |          6 |
|  4 |  'Bag 10'  |           1 |  500 |   10 |          6 |
|  8 |  'Bag 10'  |           1 |  600 |   10 |         12 |
|  9 |  'Bag 10'  |           1 |  601 |   10 |         12 |
| 10 |  'Bag 10'  |           1 |  502 |   10 |         13 |
| 11 |  'Bag 10'  |           1 |  501 |   10 |         13 |
| 15 |  'Box 40'  |           1 |  600 |   40 |         16 |
| 17 |  'Bag 10'  |           2 |  700 |   10 |         19 |
| 18 |  'Bag 10'  |           2 |  701 |   10 |         19 |
| 20 |  'Bag 10'  |           2 |  703 |   10 |         22 |
| 21 |  'Bag 10'  |           2 |  701 |   10 |         22 |
+----+------------+-------------+------+------+------------+

Agrupar por lote:

|----------|---------|-----|----------|
| Order ID | Product | Lot | Quantity |
|----------|---------|-----|----------|
|    1     |    1    | 500 |    20    |
|    1     |    1    | 501 |    20    |
|    1     |    1    | 502 |    20    |
|    1     |    1    | 600 |    50    |
|    1     |    1    | 601 |    10    |
|    1     |    2    | 700 |    10    |
|    1     |    2    | 701 |    20    |
|    1     |    2    | 703 |    10    |
|----------|---------|-----|----------|

Eu criei um exemplo de rextester com esses valores.

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Este código mostra como usar um CTE recursivo para retornar os resultados que você está procurando. 

    IF OBJECT_ID('dbo.OrdersItems') IS NOT NULL DROP TABLE dbo.OrdersItems;
IF OBJECT_ID('dbo.Containers') IS NOT NULL DROP TABLE dbo.Containers;

CREATE TABLE dbo.Containers
(
    ContainerID int NOT NULL
        CONSTRAINT PK_containers
        PRIMARY KEY CLUSTERED
    , ContainerName text NOT NULL
    , ProductID int NOT NULL
    , Lot int NULL
    , Quantity int NOT NULL
    , ParentContainerID int NULL
        CONSTRAINT FK_Containers_ContainerID
        FOREIGN KEY 
        REFERENCES dbo.Containers (ContainerID)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

CREATE TABLE dbo.OrdersItems 
(
      OrderID INT NOT NULL
    , Position int NOT NULL
    , ContainerID int NULL
        CONSTRAINT FK_OrdersItems_ContainerID
        FOREIGN KEY
        REFERENCES dbo.Containers (ContainerID)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
    , CONSTRAINT PK_OrdersItems 
        PRIMARY KEY CLUSTERED (OrderID, Position)
);

    Insira os dados de amostra:

    INSERT INTO dbo.Containers (ContainerID, ContainerName, ProductID
                             , Lot, Quantity, ParentContainerID)
VALUES 
      (23, 'Box 40',  2, null, 40,  null)
    , (16, 'Pallet',  1, null, 120, null)
    , (12, 'Bag 20',  1, null, 20,  14)
    , (13, 'Bag 20',  1, null, 20,  14)
    , (14, 'Box 40',  1, null, 40,  16)
    , (19, 'Bag 20',  2, null, 20,  23)
    , (22, 'Bag 20',  2, null, 20,  23)
    , (5,  'Bag 20',  1, null, 20,  7)
    , (6,  'Bag 20',  1, null, 20,  7)
    , (7,  'Box 40',  1, null, 40,  16)
    , (1,  'Bag 10',  1, 500,  10,  5)
    , (2,  'Bag 10',  1, 501,  10,  5)
    , (3,  'Bag 10',  1, 502,  10,  6)
    , (4,  'Bag 10',  1, 500,  10,  6)
    , (8,  'Bag 10',  1, 600,  10,  12)
    , (9,  'Bag 10',  1, 601,  10,  12)
    , (10, 'Bag 10',  1, 502,  10,  13)
    , (11, 'Bag 10',  1, 501,  10,  13)
    , (15, 'Box 40',  1, 600,  40,  16)
    , (17, 'Bag 10',  2, 700,  10,  19)
    , (18, 'Bag 10',  2, 701,  10,  19)
    , (20, 'Bag 10',  2, 703,  10,  22)
    , (21, 'Bag 10',  2, 701,  10,  22);

INSERT INTO dbo.OrdersItems (OrderID, Position, ContainerID)
VALUES (1, 1, 16)
    , (1, 2, 23);

    Aqui está o CTE recursivo: 

    ;WITH RecursiveCTE AS
(
    SELECT c1.ContainerID
        , c1.ContainerName
        , c1.Lot
        , c1.ParentContainerID
        , c1.ProductID
        , c1.Quantity
        , Level = 1
    FROM dbo.Containers c1
    WHERE c1.ParentContainerID IS NULL
    UNION ALL
    SELECT c2.ContainerID
        , c2.ContainerName
        , c2.Lot
        , c2.ParentContainerID
        , c2.ProductID
        , c2.Quantity
        , Level = RecursiveCTE.Level + 1
    FROM dbo.Containers c2
        INNER JOIN RecursiveCTE ON RecursiveCTE.ContainerID = c2.ParentContainerID
)
SELECT r.ProductID
    , r.Lot
    , QuantityTotal = SUM(r.Quantity)
FROM RecursiveCTE r 
WHERE r.Lot IS NOT NULL
    AND r.Level > 1
GROUP BY r.ProductID
    , r.Lot;

    Os resultados:

    ProductID | Lot | QuantityTotal
--------: | --: | ------------:
        1 | 500 |            20
        1 | 501 |            20
        1 | 502 |            20
        1 | 600 |            50
        1 | 601 |            10
        2 | 700 |            10
        2 | 701 |            20
        2 | 703 |            10

    Como você pode ver no código acima, renomeei algumas de suas colunas para refletir mais claramente seu conteúdo e adicionei uma formatação menor, juntamente com referências de chave estrangeira.

    db<>mexa aqui

    • 1

relate perguntas