从最小的金额开始扣除，直到金额耗尽

Question

我有一个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

当选择供应表时从最小的发票编号开始扣除保留金额，直到金额耗尽

例外的输出应该是

tbody> <<tr>

Supplier_Name	Invoice_Number	Amount
Supply1	Inv1	0.00
Supply1	Inv2	500
Supply1	Inv3	3000
Supply2	Inv1	500.00
Supply2	Inv2	2500.00

使用窗口函数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&lt的在小提琴演示

顺便说一下，你在问题中期望的输出是错误的

应该

Inv11000.00000.00002000.0000500.00001000.0000