SQL累积值

我正在编写一个 SQLServer 2008 存储过程，它采用一个付款表并尝试根据相关表中描述的一组规则(基本上是一组存储桶)分配这些付款。但是，分配(将付款值放入桶中)是目前让我头疼的原因。

假设表 Payments 包含要支付的值，而表 Buckets 则是关于在要支付的初始值耗尽(达到 0)之前应将多少放入每个桶中。

使用下表作为示例(实际用例有点人为，因为有一些复杂的标准来选择适合每次付款的存储桶):

PaymentId     Value                 BucketId       MaxAmount
--------------------------          --------------------------------
1             16.5                  1              5.5
2             7.0                   2              10
                                    3              8.3

对于付款 1:应将 5.5 个单位(该桶的最大值)放入桶 1，将 10 个单位放入桶 2(11.5 是桶 1 的剩余部分，但仍超过桶 2 的最大值)和 1 个单位(16.5 - 5.5 - 10 ) 应放入桶 3。对所有付款重复此操作。

这很容易在任何命令式语言中实现，甚至可能在带有 for/while 循环的 SQL 中实现，但我试图意识到是否有更好的方法(即使它不可移植且特定于 SQLServer 2005+)。

我已经做了一些研究(主要是递归 CTE)，但没有想到真正出色的东西。我敢肯定有很多 StackOverflowers 和 SQL-fu 可以从他们的头脑中回答这个问题，所以我想把它放在那里看看......

非常感谢你的帮助。

最佳答案

我不使用游标的尝试:

DECLARE @Buckets TABLE (
    BucketId INT,
    MaxAmount DECIMAL(18,6)
)

INSERT INTO @Buckets VALUES (1, 5.5)
INSERT INTO @Buckets VALUES (2, 10)
INSERT INTO @Buckets VALUES (3, 8.3)

DECLARE @Payments TABLE (
    PaymentId INT,
    Value DECIMAL(18,6)
)

INSERT INTO @Payments VALUES (1,16.5)
INSERT INTO @Payments VALUES (2,7.0)

SELECT
  P1.PaymentId
, P1.Value as TotalPayment
, B4.BucketId
, B4.MaxAmount
, CASE WHEN B3.BucketId = B4.BucketId THEN P1.Value - MaxAmountRunningTotalOfPreviousBuckets ELSE B4.MaxAmount END AS BucketPaymentAmount
FROM @Payments P1
INNER JOIN (
    SELECT
      B2.BucketId
    , B2.MaxAmount as BucketMaxAmount
    , SUM(B1.MaxAmount) - B2.MaxAmount as MaxAmountRunningTotalOfPreviousBuckets
    FROM @Buckets B1
    INNER JOIN @Buckets B2
      ON B1.BucketId <= B2.BucketId
    GROUP BY B2.BucketId, B2.MaxAmount
  ) AS B3
  ON P1.Value > B3.MaxAmountRunningTotalOfPreviousBuckets AND P1.Value <= (B3.MaxAmountRunningTotalOfPreviousBuckets + BucketMaxAmount)
INNER JOIN @Buckets B4
  ON B4.BucketId <= B3.BucketId
ORDER BY P1.PaymentId, B3.BucketId

关于SQL累积值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11621618/