一种方法是使用
LEFT JOIN
来计算
ValueBefore
的累计总和。
这个查询:
SELECT t2.ID, t2.GroupID, t2.ValueBefore, r.ValueBefore AS rValueBefore
FROM Table2 AS t2
LEFT JOIN Table2 AS r
ON t2.GroupID = r.GroupID AND t2.ID >= r.ID
ORDER BY t2.ID, r.ID
会产生以下输出:
ID GroupID ValueBefore rValueBefore
------------------------------------
1 1 10 10
2 1 15 10
2 1 15 15
3 1 5 10
3 1 5 15
3 1 5 5
4 1 5 10
4 1 5 15
4 1 5 5
4 1 5 5
5 2 40 40
6 2 30 40
6 2 30 30
如果你按ID
分组并在rValueBefore
上使用SUM
,现在可以计算运行总数:
SELECT t2.ID, t2.GroupID, t2.ValueBefore, SUM(r.ValueBefore) AS Total
FROM Table2 AS t2
LEFT JOIN Table2 AS r
ON t2.GroupID = r.GroupID AND t2.ID >= r.ID
GROUP BY t2.ID, t2.GroupID, t2.ValueBefore
输出:
ID GroupID ValueBefore Total
-----------------------------
1 1 10 10
2 1 15 25
3 1 5 30
4 1 5 35
5 2 40 40
6 2 30 70
现在您可以使用以下代码计算ValueAfter
:
SELECT t.ID, t.GroupID, t.ValueBefore, Total,
Subtracted,
CASE
WHEN Total - t1.Subtracted < 0 THEN 0
ELSE Total - t1.Subtracted
END AS ValueAfter
FROM
(
SELECT t2.ID, t2.GroupID, t2.ValueBefore, SUM(r.ValueBefore) AS Total
FROM Table2 AS t2
LEFT JOIN Table2 AS r
ON t2.GroupID = r.GroupID AND t2.ID >= r.ID
GROUP BY t2.ID, t2.GroupID, t2.ValueBefore
) as t
JOIN Table1 AS t1 ON t.GroupID = t1.GroupID
ORDER BY ID
输出:
ID GroupID ValueBefore Total Subtracted ValueAfter
-------------------------------------------------------
1 1 10 10 32 0
2 1 15 25 32 0
3 1 5 30 32 0
4 1 5 35 32 3
5 2 40 40 30 10
6 2 30 70 30 40
这里有演示