更新行组，以从行的总和中删除特定总金额 SQL

Question

更新行组，以从行的总和中删除特定总金额 SQL

sqlsql-serversql-updatesql-server-2014

3

在此示例中，我想从表2中按GroupID分组的ValueBefore的总和中减去表1中Subtracted值。对第一行进行减法操作，如果有余数，则继续在同一分组中的下一行中进行减法操作（以此类推），直到减去总和为止。

是否有一种不需要迭代行来获取余数的方法？

我的表1：

减值的 TABLE 1
GroupID    Subtracted
  1          32
  2          30

我的表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

- James Hogg

3

你正在使用哪种关系型数据库管理系统？ MySQL、SQL Server 还是其他的？ - Giorgos Betsos

SQL Server 2014 - 抱歉，我的标签没有捕捉到。 - James Hogg

2个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Giorgos Betsos · Answer 1

一种方法是使用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

这里有演示

- Gordon Linoff · Answer 2

我会使用累加和和一些相对简单的比较：

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;