| 命令 | 數量 | 存貨 | 型別 |
|---|---|---|---|
| 1001 | -1 | -1 | 顧客訂單 |
| 1002 | -1 | -2 | 顧客訂單 |
| 1003 | -1 | -3 | 顧客訂單 |
| 1004 | -1 | -4 | 顧客訂單 |
| 1005 | -5 | -9 | 顧客訂單 |
| 1006 | 3 | -6 | 采購訂單 |
| 1007 | 6 | 0 | 采購訂單 |
通過上表，我們可以找到滿足客戶訂單的采購訂單編號。
例子：
| 命令 ＃ | 數量 | 存貨 | 型別 | 將履行訂單的采購訂單# |
|---|---|---|---|---|
| 1001 | -1 | -1 | 顧客訂單 | 1006 |
| 1002 | -1 | -2 | 顧客訂單 | 1006 |
| 1003 | -1 | -3 | 顧客訂單 | 1006 |
| 1004 | -1 | -4 | 顧客訂單 | 1007 |
| 1005 | -5 | -9 | 顧客訂單 | 1007 |
| 1006 | 3 | -6 | 采購訂單 | |
| 1007 | 6 | 0 | 采購訂單 |
SQL
SELECT *
, CASE WHEN Inventory >= 0 THEN OrderNumber
ELSE LEAD(CASE WHEN QTY > 0 THEN OrderNumber END) IGNORE NULLS
OVER(ORDER BY OrderNumber)
END PurchaseOrderNoToFulfil
FROM OrderTable;
上述查詢不會跟蹤已使用的采購訂單。例如，它顯示了客戶訂單 #1004 和 1005 的采購訂單 #1006。由于 1006 只有 3 個數量可用，因此它最多滿足訂單 1003，但不能超過此數量。
For more context:
On order 1001, the inventory is -1 (oversold). Order 1002, the inventory is -2 and so on. On order 1006, there is an incoming purchase order of Qty 3 to fulfil the previous customer orders. But the quantity of 3 can only fulfil upto order 1003. Order 1004 and 1005 will have to wait until 1007 PO comes in.
uj5u.com熱心網友回復：
我認為這給了我符合你要求的結果。也許你會發現它很有用。
with C as (
select ordernum, -inventory as inventory,
sum(-qty) over (order by ordernum) as fulfilled
from O where "Type" = 'Customer Order'
), P as (
select *, sum(qty) over (order by ordernum) as POed
from O where "Type" = 'Purchase Order')
select ordernum, inventory, PO, POed
from C outer apply (
select min(ordernum) as PO, min(POed) as POed from P where POed >= fulfilled
) oa
https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=25a4efd31efc0827940a83b8658eb2b2
用標量子查詢重寫：
with C as (
select ordernum, -inventory as inventory,
sum(-qty) over (order by ordernum) as fulfilled
from O where "Type" = 'Customer Order'
), P as (
select *, sum(qty) over (order by ordernum) as POed
from O where "Type" = 'Purchase Order')
select ordernum, inventory,
(select min(ordernum) as PO from P where POed >= fulfilled) as PO
from C
uj5u.com熱心網友回復：
您可以使用遞回 CTE：
with recursive orders as (
select row_number() over (order by `order`) o, * from ords
),
cte(id, r_id, s) as (
select min(o.o), o1.o, o.qty o1.qty from orders o
join orders o1 on o1.qty o.qty >= 0 where o.qty < 0 and o1.qty = (select min(o2.qty) from orders o2 where o2.qty > 0)
union all
select c.id 1, case when c.s o1.qty >= 0 or o3.o is null then c.r_id else o3.o end,
case when c.s o1.qty >= 0 then c.s o1.qty else c.s o1.qty o3.qty end
from cte c join orders o1 on c.id 1 = o1.o
left join orders o3 on o3.o > c.r_id where c.id 1 <= (select max(o2.o) from orders o2 where o2.qty < 0)
)
select o.*, c.r_id from orders o left join cte c on o.o = c.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/353906.html
標籤：sql
上一篇：OracleQuery：如何在插入記錄時創建唯一鍵？
下一篇：按計數和日期的SQL分組