sql - 是否可以在不循环的情况下根据另一个表减去多行？

Question

假设我有这张表 Rewards

CustomerID DateReceived Point
         1   2020-01-01    15
         1   2020-01-02    20
         2   2020-01-08    20
         2   2020-01-10    10
         3   2020-01-10    50

然后我有这张表 MaxRewards

CustomerID MaxPoint
         1       20
         2       10
         3       30

因此，想法是客户的 Point 组合(求和)永远不应超过 MaxRewards 表中的 MaxPoint，如果超过，那么它将从最近的日期开始减去，所以在上面的例子中，预期的结果应该是

CustomerID DateReceived Point
         1   2020-01-01    15
         1   2020-01-02     5
         2   2020-01-08    10
         2   2020-01-10     0
         3   2020-01-10    30

如何在没有循环的情况下执行查询来更新表？我一直在考虑使用 CROSS APPLY，但我似乎做不好。

Answer 1

以下方法似乎有效:

WITH cte AS (
    SELECT
        r.CustomerID,
        r.DateReceived,
        r.Point,
        mr.MaxPoint,
        COALESCE(SUM(r.Point) OVER (PARTITION BY r.CustomerID ORDER BY r.DateReceived
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS cumPoints
    FROM Rewards r
    INNER JOIN MaxRewards mr ON r.CustomerID = mr.CustomerID
)

SELECT
    CustomerID,
    DateReceived,
    CASE WHEN cumPoints + Point < MaxPoint
         THEN Point
         ELSE CASE WHEN MaxPoint - cumPoints > 0
                   THEN MaxPoint - cumPoints ELSE 0 END END AS Point
FROM cte
ORDER BY
    CustomerID,
    DateReceived;

Demo

此处使用的逻辑是在 CTE 中计算日期序列中当前记录之前每个客户的积分总和。然后，我们将两件事之一报告为输出中的 Point。对于累计总和已经大于最大点数的情况，我们报告 0。否则，我们报告最大点数与累计总和之间的差值，它给出了点数来 self 们要添加的当前记录。