Sql-Server
如何獲取分層列表的最後一級，按某個欄位分組
給定架構：
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);哪裡
containers是具有未知級別數的分層表。containers.parent_id = containers.id OR null一個容器可以容納其他容器，這是一個容器樹結構：
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]對我們來說，一個集裝箱被視為一個遠征單位，根據客戶的要求，一個集裝箱可以是一個簡單的 10 件塑膠袋，也可以是一個裝滿 10 箱，每箱 5000 件的完整托盤。只有最後一級的容器分配了批次，父容器可以容納不同的批次，但始終屬於同一產品。
我們需要按批次列出單個訂單中包含的所有產品。
- 不同的產品不能有相同的批次。
- 一個容器只能容納相同產品的容器。
樣本數據：
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);訂單號
1有兩個詳細資訊行，容器16&23，我需要將所有批次都包含在這些容器中。在此範例中，結果應顯示以下行：
+----+------------+-------------+------+------+------------+ | 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 | +----+------------+-------------+------+------+------------+按批次分組：
|----------|---------|-----|----------| | 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 | |----------|---------|-----|----------|我用這個值創建了一個rextester範例。
此程式碼顯示如何使用遞歸 CTE 來返回您正在尋找的結果。
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) );插入樣本數據：
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);這是遞歸 CTE：
;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;結果：
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從上面的程式碼可以看出，我重命名了一些列以更清楚地反映它們的內容，並添加了一些小格式以及外鍵引用。
db<>在這裡擺弄