我有一個看起來像這樣的 SQL 表
CREATE TABLE #Temp_Supply
(
Supplier_Name Varchar(30),
Invoice_Number Varchar(20),
Amount Money
)
INSERT INTO #Temp_Supply (Supplier_Name, Invoice_Number, Amount)
SELECT 'Supply1', 'Inv1', 1000 UNION
SELECT 'Supply1', 'Inv2', 2000 UNION
SELECT 'Supply1', 'Inv3', 3000 UNION
SELECT 'Supply2', 'Inv1', 1500 UNION
SELECT 'Supply2', 'Inv2', 2500
CREATE TABLE #Temp_Supply_Deduction
(
Supplier_Name Varchar(30),
Deduction_Amount Money
)
INSERT INTO #Temp_Supply_Deduction(Supplier_Name, Deduction_Amount)
SELECT 'Supply1', 2500 UNION
SELECT 'Supply2', 500
SELECT * FROM #Temp_Supply
SELECT * FROM #Temp_Supply_Deduction
選擇供應表時從最小的發票號開始扣除保留金額，直到金額用完
例外的輸出應該是
| 供應商名稱 | 發票編號 | 數量 |
|---|---|---|
| 供應1 | Inv1 | 0.00 |
| 供應1 | Inv2 | 500 |
| 供應1 | Inv3 | 3000 |
| 供應2 | Inv1 | 500.00 |
| 供應2 | Inv2 | 2500.00 |
謝謝
uj5u.com熱心網友回復：
使用視窗函式sum() over (partition by ... order by ...)得到 的累積和Amount。使用CASE運算式有條件地檢查Deduction_Amount累計金額以確定余額
select d.Supplier_Name,
d.Deduction_Amount,
s.Invoice_Number,
Invoice_Amount = s.Amount,
Balance = case when d.Deduction_Amount
>= sum(s.Amount) over (partition by d.Supplier_Name
order by s.Amount,
s.Invoice_Number)
then 0
when d.Deduction_Amount
>= sum(s.Amount) over (partition by d.Supplier_Name
order by s.Amount,
s.Invoice_Number)
- s.Amount
then sum(s.Amount) over (partition by d.Supplier_Name
order by s.Amount,
s.Invoice_Number)
- d.Deduction_Amount
else s.Amount
end
from #Temp_Supply_Deduction d
inner join #Temp_Supply s on d.Supplier_Name = s.Supplier_Name
db<>小提琴演示
順便說一句，您在問題中的預期輸出是錯誤的
應該
| 供應商名稱 | 扣減金額 | 發票編號 | 發票_金額 | 平衡 |
|---|---|---|---|---|
| 供應1 | 2500.0000 | Inv1 | 1000.0000 | 0.0000 |
| 供應1 | 2500.0000 | Inv2 | 2000.0000 | 500.0000 |
| 供應1 | 2500.0000 | Inv3 | 3000.0000 | 3000.0000 |
| 供應2 | 500.0000 | Inv1 | 1500.0000 | 1000.0000 |
| 供應2 | 500.0000 | Inv2 | 2500.0000 | 2500.0000 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/477202.html
下一篇：從另一個表中的列更新列部分匹配