如何将多笔 payments 分配到 invoice lines
结论
这里不能只比较两组累计值。需要把每个 invoice line 视为累计金额轴上的一个区间，再用 payment 的累计边界切分这些区间。
第一笔 payment 的区间下界不能固定为 0，因为 invoice line 可能是负数。它必须覆盖付款累计轴起点之前的负数区间，分配结果才会是：
-50全部分配给第一笔 payment；- 然后分配
40； - 第一笔 payment 还能向金额为
100的行分配60，此时净分配金额为-50 + 40 + 60 = 50； - 第二笔 payment 分配剩余的
40。
下面的查询适用于题目给出的规则：payment 为非负数，invoice lines 按 itemValue, lineId 升序分配。
查询实现
WITH invoice_lines AS
(
SELECT
s.lineId,
s.invoiceId,
s.itemValue,
lineStart =
COALESCE(
SUM(s.itemValue) OVER
(
PARTITION BY s.invoiceId
ORDER BY s.itemValue, s.lineId
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING
),
CONVERT(money, 0)
),
lineEnd =
SUM(s.itemValue) OVER
(
PARTITION BY s.invoiceId
ORDER BY s.itemValue, s.lineId
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)
FROM dbo.#sales AS s
),
payment_totals AS
(
SELECT
p.paymentId,
p.invoiceId,
p.PayAmount,
paymentNo =
ROW_NUMBER() OVER
(
PARTITION BY p.invoiceId
ORDER BY p.paymentId
),
paymentStart =
COALESCE(
SUM(p.PayAmount) OVER
(
PARTITION BY p.invoiceId
ORDER BY p.paymentId
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING
),
CONVERT(money, 0)
),
paymentEnd =
SUM(p.PayAmount) OVER
(
PARTITION BY p.invoiceId
ORDER BY p.paymentId
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)
FROM dbo.#payments AS p
),
allocations AS
(
SELECT
i.lineId,
i.invoiceId,
i.itemValue,
i.lineStart,
i.lineEnd,
p.paymentId,
p.paymentNo,
p.paymentStart,
p.paymentEnd,
valuePaid =
CONVERT
(
money,
CASE
/*
* 负数行按约定全部归入第一笔 payment。
* 它会增加第一笔 payment 后续可分配给正数行的金额。
*/
WHEN i.itemValue < 0
AND p.paymentNo = 1
THEN i.itemValue
/*
* 正数行与 payment 累计区间求交集。
* 第一笔 payment 没有 0 这个固定下界，
* 因而可以覆盖负累计值之后的正数行。
*/
WHEN i.itemValue > 0
THEN
CASE
WHEN
CASE
WHEN i.lineEnd < p.paymentEnd
THEN i.lineEnd
ELSE p.paymentEnd
END
>
CASE
WHEN p.paymentNo = 1
THEN i.lineStart
WHEN i.lineStart > p.paymentStart
THEN i.lineStart
ELSE p.paymentStart
END
THEN
CASE
WHEN i.lineEnd < p.paymentEnd
THEN i.lineEnd
ELSE p.paymentEnd
END
-
CASE
WHEN p.paymentNo = 1
THEN i.lineStart
WHEN i.lineStart > p.paymentStart
THEN i.lineStart
ELSE p.paymentStart
END
ELSE 0
END
ELSE 0
END
)
FROM invoice_lines AS i
INNER JOIN payment_totals AS p
ON p.invoiceId = i.invoiceId
),
allocated_rows AS
(
SELECT
a.lineId,
a.invoiceId,
a.itemValue,
a.lineStart,
a.paymentId,
a.paymentNo,
a.valuePaid
FROM allocations AS a
WHERE a.valuePaid <> 0
)
SELECT
lineId,
invoiceId,
value = itemValue,
paymentId,
valuePaid,
valueUnpaid =
itemValue
- SUM(valuePaid) OVER
(
PARTITION BY invoiceId, lineId
ORDER BY paymentNo
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)
FROM allocated_rows
ORDER BY
invoiceId,
lineStart,
lineId,
paymentNo;查询结果如下：
lineId invoiceId value paymentId valuePaid valueUnpaid
------ --------- ------- --------- --------- -----------
2 1 -50.00 1 -50.00 0.00
3 1 40.00 1 40.00 0.00
1 1 100.00 1 60.00 40.00
1 1 100.00 2 40.00 0.00
4 2 500.00 3 300.00 200.00
为什么原查询无法处理负数
原查询把 invoice line 和 payment 都转换为累计区间，再计算区间交集。只要所有 invoice line 都是正数，这种方法就能成立，因为累计值会一直递增。
加入 -50 后，invoice 的累计值变为：
line 2: 0 -> -50
line 3: -50 -> -10
line 1: -10 -> 90
第一笔 payment 按传统方式得到的区间是：
0 -> 50
直接计算区间交集时：
0 -> -50是反向区间；-50 -> -10完全位于 payment 区间下方；- 只有
-10 -> 90与0 -> 50相交。
这样一来，原查询会错误地把第一笔 payment 的 50 全部分配给 lineId = 1。
修改后的查询不再为第一笔 payment 设置固定的左边界，并明确将负数行分配给第一笔 payment。因此，第一笔 payment 的净分配金额是：
-50 + 40 + 60 = 50
正好等于 payment 金额。
排序必须具有确定性
排序条件不能只写：
ORDER BY itemValue如果两条 invoice line 的 itemValue 相同，SQL Server 不保证它们的顺序。需要加入唯一列处理相同值：
ORDER BY itemValue, lineIdproduction tables 中的 payment 也通常应该按实际业务时间排序，例如：
ORDER BY paymentDate, paymentId否则总分配金额可能仍然正确，但无法稳定判断每笔 payment 具体支付了哪些 invoice lines。
负数 payment 的处理
上面的查询只处理示例中的规则，也就是 invoice lines 可以同时包含正数和负数，但 payment 必须为非负数。
如果 invoice 总额为负数，而且 payment 本身也允许为负数，就必须先确定负数 payment 的业务含义。它可能表示退款、贷项结算或反向付款，不同含义对应不同的分配顺序，不能直接沿用正数 payment 的累计区间算法。
如果业务规则明确规定”负数 payment 只分配给负数 invoice lines”，可以把这类 invoice 的 line 和 payment 同时乘以 -1，在正数空间中完成分配，最后再将 valuePaid 乘以 -1。
如果同一 invoice 同时包含正负 payment，或者负数 payment 可以冲销正数 line，就需要另行定义冲销顺序。这类情况通常更适合用递归 CTE 或逐笔分配过程处理。
备注：内容仅供参考。