我試圖從 2 個資料表中映射訂單/專案數量。
表一：
order | item | Qty
------------------
ABC | IA1 | 5
ABC | IA2 | 6
ABC | IA3 | 1
ABD | IA4 | 2
ABD | IA5 | 2
ABE | IA6 | 1
ABE | IA7 | 2
表乙：
order | Qty
-----------
ABC | 9
ABD | 3
ABE | 3
輸出應該是：
order | item | ordered_Qty
---------------------------
ABC | IA1 | 5 ( 5 units from 9 )
ABC | IA2 | 4 ( remaining 4 )
ABC | IA3 | 0 ( 0 as 0 is remaining )
ABD | IA4 | 2 (2 out of 3 )
ABD | IA5 | 1 (remaining 1 )
ABE | IA6 | 1 ( 1 out of 3 )
ABE | IA7 | 2 ( remaining 2 )
uj5u.com熱心網友回復：
SELECT t1.*,
CASE WHEN t2.Qty < SUM(t1.Qty) OVER (PARTITION BY `order` ORDER BY t1.item)
THEN GREATEST(t2.Qty t1.Qty - SUM(t1.Qty) OVER (PARTITION BY `order` ORDER BY t1.item), 0)
ELSE t1.Qty
END ordered_Qty
FROM t1
JOIN t2 USING (`order`)
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=35ef35456c7d1910ea998131ce02d76b
uj5u.com熱心網友回復：
對于支持視窗函式（8 ）的 MySQL 版本，這應該是相當簡單的：
WITH a2 as (
SELECT *,
SUM(Qty) OVER(PARTITION BY [order] ORDER BY item) csum
FROM TableA
)
SELECT *,
CASE WHEN b.Qty >= csum THEN a2.Qty --b.Qty can fully cover the cumulative sum, put the a.Qty
WHEN b.Qty >= csum - a2.Qty THEN a2.Qty - (csum-b.Qty) --b.Qty can partially cover it, put the a2.Qty minus the "overspend"
ELSE 0 --b.Qty "already spent"
END as diff
FROM
a2 INNER JOIN TableB b ON a2.[order] = b.[order]
這item讓我有點困擾，因為如果某些order內容包含例如IA2和IA10- 您可能必須通過將 IA 替換為（無）的 int 轉換進行排序
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/359834.html