给定一个包含发票列表的表和一个包含付款列表的表,我遇到以下问题:
- 付款应分配至发票,最多可达发票总金额。
- 如果付款金额大于发票上的剩余金额,则余额应分配到下一张发票。
- 如果没有更多发票,则可能会超出发票总金额。所有剩余付款均可分配至最终发票。
例如,给出下表:
发票编号 | 发票金额 |
---|---|
1 | 5.00 |
2 | 5.00 |
3 | 5.00 |
付款ID | 支付金额 |
---|---|
1 | 4.00 |
2 | 4.00 |
3 | 4.00 |
4 | 4.00 |
期望的结果如下:
发票编号 | 发票金额 | 付款ID | 支付金额 |
---|---|---|---|
1 | 5.00 | 1 | 4.00 |
1 | 5.00 | 2 | 1.00 |
2 | 5.00 | 2 | 3.00 |
2 | 5.00 | 3 | 2.00 |
3 | 5.00 | 3 | 2.00 |
3 | 5.00 | 4 | 4.00 |
我能想到的最佳解决方案涉及循环和/或游标,但我的直觉告诉我,有一种更好的方法来做到这一点,但我只是没有看到。
这是一种方法(Fiddle)。
您没有指定任何特定版本,因此我使用了 SQL Server 2022 语法。如果您使用的是以前的版本,则使用的所有功能的类似物都是可能的,但很麻烦。
它为所有付款 (P) 和所有发票 (I) 发出“之前”和“之后”行,并按累计总额的顺序将它们组合起来。
然后,它提取所有“之后”行(“1”后缀) - 表示发票已全额支付或付款已用完,并用于查找
LAST_VALUE
当时适用的 InvoiceId/PaymentId。例如对于以下输入数据
它把它变成以下形式
返回结果