sql - 具有最大限制的运行总计

Question

我有一个简单的问题，但解决起来有点困难。我想总结一个特定的列，直到它达到限制并自行重置。 (SQL 2012)

假设限制为 50

 - List item
 - Value    Total
 - 10        10
 - 20        30
 - 30        60
 - 40        50   (60-limit) + the current row value
 - 2         2
 - 3         5
 - 10        15
 - 25        40
 - 15        55
 - 5         10 (55-limit) + the current row value

非常感谢。

Answer 1

如果您有 SQL Server 2012 或更高版本，则应该这样做:

DECLARE @Table TABLE (Id INT, ListItem INT);
INSERT INTO @Table VALUES (1, 10);
INSERT INTO @Table VALUES (2, 20);
INSERT INTO @Table VALUES (3, 30);
INSERT INTO @Table VALUES (4, 40);
INSERT INTO @Table VALUES (5, 2);
INSERT INTO @Table VALUES (6, 3);
INSERT INTO @Table VALUES (7, 10);
INSERT INTO @Table VALUES (8, 25);
INSERT INTO @Table VALUES (9, 15);
INSERT INTO @Table VALUES (10, 5);
WITH RunningTotal AS (
SELECT Id, ListItem, SUM(ListItem) OVER (ORDER BY Id) % 50 AS RT FROM @Table)
SELECT
    rt.Id,
    rt.ListItem,
    CASE WHEN rt.RT < rt2.RT THEN rt.RT + 50 ELSE rt.RT END AS RunningTotal
FROM
    RunningTotal rt
    LEFT JOIN RunningTotal rt2 ON rt2.Id = rt.Id - 1
ORDER BY
    rt.Id;

棘手的一点是允许数字一次溢出 50，否则这将是微不足道的。

结果是:

Id  LI  RunningTotal
1   10  10
2   20  30
3   30  60
4   40  50
5   2   2
6   3   5
7   10  15
8   25  40
9   15  55
10  5   10