SQL Server帐单中的金额分配 [英] SQL Server Allocation of amount in bill

问题描述

我有下表

LINEID   BILL   Total Amount     Allocated Amount
1         1         100             
2         1         200
3         2         250

PAYID    BILL    Paid Amount
1         1         250
2         2         100

我需要根据帐单在第一张桌子上分配已付费".我知道我可以在WHILE循环中使用游标并进行分配-有更好的方法吗?

I need to allocate the Paid amoount on the first table based on the bill. I know that I can use the cursor with WHILE loop and allocate - is there a better way to do this?

结果应为

LINEID   BILL   Total Amount     Allocated Amount
1         1         100             100
2         1         200         150
3         2         250         100

推荐答案

WITH  cl AS
(select o.LINEID, o.BILL, o.[Total Amount], 
(select SUM([Total Amount]) from bills t where o.BILL = t.BILL and o.LINEID >= t.LINEID) as 'sum_total_ammount'
from bills o inner join payment p on o.BILL = p.BILL)

select o.LINEID, o.BILL, o.[Total Amount], 
case when p.[Paid Amount] >= sum_total_ammount then o.[Total Amount] 
else (o.[Total Amount] - sum_total_ammount + p.[Paid Amount]) end as 'Allocated Amount'
from cl o inner join payment p on o.BILL = p.BILL
and (o.[Total Amount] - sum_total_ammount + p.[Paid Amount]) > 0

这是 SqlFiddle 上的演示.

这篇关于SQL Server帐单中的金额分配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！