下面我們有兩個表，一個是采購訂單，另一個是銷售訂單。我想要做的是將每個銷售訂單分配給一個采購訂單，并提供免費庫存。我可以用以下查詢來做：
表 1 - 進貨采購訂單：
| 數字 | 物品 | 發貨日期 | 數量 | 使用數量 | 自由數量 |
|---|---|---|---|---|---|
| 12 | 玩具 | 2021-11-20 | 100 | 95 | 5 |
| 22 | 玩具 | 2021-11-24 | 230 | 190 | 40 |
| 23 | 玩具 | 2021-11-27 | 145 | 140 | 140 |
| 34 | 玩具 | 2021-12-20 | 400 | 400 | 400 |
表 2 - 銷售訂單：
| 數字 | 物品 | 創建日期 | 需要數量 | 分配到PoNum |
|---|---|---|---|---|
| 1234 | 玩具 | 2021-06-03 | 3 | |
| 2345 | 玩具 | 2021-08-09 | 2 | |
| 3456 | 玩具 | 2021-08-26 | 30 | |
| 4567 | 玩具 | 2021-08-31 | 6 | |
| 4574 | 玩具 | 2021-09-02 | 4 | |
| 5685 | 玩具 | 2021-10-13 | 100 |
SELECT
a.number,
a.item,
a.createDate,
a.qtyNeeded,
(SELECT TOP 1 x.number FROM purchaseOrders x WHERE a.item = x.item ORDER BY x.createDate, x.number) as 'allocateToPoNum'
FROM salesOrder a
ORDER BY a.createDate
這將回傳以下內容：
| 數字 | 物品 | 創建日期 | 需要數量 | 分配到PoNum |
|---|---|---|---|---|
| 1234 | 玩具 | 2021-06-03 | 3 | 12 |
| 2345 | 玩具 | 2021-08-09 | 2 | 12 |
| 3456 | 玩具 | 2021-08-26 | 30 | 12 |
| 4567 | 玩具 | 2021-08-31 | 6 | 12 |
| 4574 | 玩具 | 2021-09-02 | 4 | 12 |
| 5685 | 玩具 | 2021-10-13 | 100 | 12 |
我遇到但似乎無法想到解決方案的問題是查詢只會回傳串列中的第一個采購訂單，但到第三行，該采購訂單的所有免費數量都用完了。
購買 12 有 5 個 freeQty。銷售訂單 1234 和 2345 總共需要 5 個數量。他們都應該有一個allocateToPoNum = 12。
銷售訂單 3456、4567 和 4574 總共需要 40 個數量，它們不能分配到采購訂單 12，因為這些數量現在都被前面的行用完了。所以應該有一個allocateToPoNum = 22
我想要發生的是，一旦所選采購訂單的所有免費數量都用完，查詢應該使用下一個帶有免費庫存的采購訂單，依此類推。所以查詢的輸出應該是這樣的：
| 數字 | 物品 | 創建日期 | 需要數量 | 分配到PoNum |
|---|---|---|---|---|
| 1234 | 玩具 | 2021-06-03 | 3 | 12 |
| 2345 | 玩具 | 2021-08-09 | 2 | 12 |
| 3456 | 玩具 | 2021-08-26 | 30 | 22 |
| 4567 | 玩具 | 2021-08-31 | 6 | 22 |
| 4574 | 玩具 | 2021-09-02 | 4 | 22 |
| 5685 | 玩具 | 2021-10-13 | 100 | 23 |
任何有關如何解決此問題的想法將不勝感激。我試圖盡可能詳細，但我遺漏了任何內容，請告訴我。
謝謝你。
uj5u.com熱心網友回復：
這似乎適用于您的資料。我只是在每個表中添加了一列來計算先前的總和，以使事情變得更容易：
declare @incomingPO table (
number int,
item varchar(20),
shipDate date,
qty int,
priorQty int)
insert into @incomingPO select 12, 'toy', '2021-11-20', 5,0
insert into @incomingPO select 22, 'toy', '2021-11-24', 40,0
insert into @incomingPO select 23, 'toy', '2021-11-27', 140,0
insert into @incomingPO select 34, 'toy', '2021-12-20', 400,0
update @incomingPO set priorQty = (select sum(qty) from @incomingPO s where s.number <= po.number) from @incomingPO po
select * from @incomingPO
declare @salesOrders table (
number int,
item varchar(20),
createDate date,
qtyNeeded int,
priorUsedQty int
)
insert into @salesOrders select 1234, 'Toy', '2021-06-03', 3, 0
insert into @salesOrders select 2345, 'Toy', '2021-08-09', 2, 0
insert into @salesOrders select 3456, 'Toy', '2021-08-26', 30, 0
insert into @salesOrders select 4567, 'Toy', '2021-08-31', 6, 0
insert into @salesOrders select 4574, 'Toy', '2021-09-02', 4, 0
insert into @salesOrders select 5685, 'Toy', '2021-10-13', 100, 0
update @salesOrders SET priorUsedQty = (select sum(qtyNeeded) FROM @salesOrders s where s.number <= so.number) from @salesOrders so
select
so.*,
(select top 1 number from @incomingPO po where so.priorUsedQty <= po.priorQty ) as [allocateToPoNum]
from @salesOrders so
輸出一個表，其中分配給銷售訂單的 PO 編號不斷增加：
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/360903.html