问题:
In example I want to subtract the Subtracted value in TABLE 1 from the Sum Total of the ValueBefore in Table 2 grouped by the GroupID. Value subtracted from first row and i...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
In example I want to subtract the Subtracted
value in TABLE 1
from the Sum Total of the ValueBefore
in Table 2 grouped by the GroupID
. Value subtracted from first row and if remainder then that is subtracted off the following row in group (and so on) until total is subtracted.
Is there a way to do this without iterating through rows to get the remainder?
My table 1:
TABLE 1 of subtracting value
GroupID Subtracted
1 32
2 30
My table 2:
ID GroupID ValueBefore Reduction ValueAfter
1 1 10 -10 0
2 1 15 -15 0
3 1 5 -5 0
4 1 5 -2 3
5 2 40 -30 10
6 2 30 0 30
回答1:
One method is to use a LEFT JOIN
in order to calculate the running total of ValueBefore
.
This query:
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
produces this output:
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
You can now calculate the running total if you group by ID
and SUM
on rValueBefore
:
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
Output:
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
You can now calculate ValueAfter
using:
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
Output:
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
Demo here
回答2:
I would use a cumulative sum and some relatively simple comparisons:
select t2.*,
(case when t2.running_value - t2.valuebefore < t1.subtracted
then 0
when t2.running_value < t1.subtracted
then t2.running_value - t1.subtracted
else t2.valuebefore
end) as reduction,
(case when t2.running_value < t1.subtracted then 0
else t2.running_value - t1.subtracted
end) as ValueAfter
from (select t2.*,
sum(valuebefore) over (partition by groupid order by id) as running_value
from t2
) t2 join
t1
on t2.groupid = t1.groupid;