Update group of rows to delete a specific total amount from the sum of the rows SQL

问题: 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;
  • 发表于 2018-07-05 11:58
  • 阅读 ( 303 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除