将值减去多行 [英] Subtract value to multiple rows

问题描述

好吧，我陷入了需要在多个行之间分配值的问题.由于我不知道具体术语，因此我将其以下面的示例形式进行介绍，以便更好地理解:

Well I am stuck at a point where I need to distribute a value across multiple rows. Since I do not know the specific term, I would put it in the form of example below for better understanding:

假设x的值为20，我需要按降序将其分配/减去到行中.

Assuming the value of x to be 20, I need to distribute/subtract it to rows in descending order.

表格:

ID        Value1 
1          6             
2          5
3          4
4          3
5          9

结果应类似于:(x = 20)

Result should look like: (x=20)

ID        Value1     Answer
1          6           14    
2          5           9
3          4           5
4          3           2
5          9           0

任何人都可以给我一个想法，我该怎么做吗?

Can anyone just give me an idea how I could go with this?

推荐答案

以不同的方式考虑这个问题也许更容易.您要计算value1的累加和，然后从@X中减去该值.如果差异为负，则放入0.

It is perhaps easier to think of this problem in a different way. You want to calculate the cumulative sum of value1 and then subtract that value from @X. If the difference is negative, then put in 0.

如果您使用的是SQL Server 2012，则您具有内置的累计总和.您可以按照以下方式进行操作:

If you are using SQL Server 2012, then you have cumulative sum built-in. You can do this as:

select id, value1,
       (case when @X - cumvalue1 < 0 then 0 else @X - cumvalue1 end) as answer
from (select id, value1,
             sum(value1) over (order by id) as cumvalue1
      from table t
     ) t;

如果您没有累积总和，则可以使用子查询来代替:

If you don't have cumulative sum, you can do this with a subquery instead:

select id, value1,
       (case when @X - cumvalue1 < 0 then 0 else @X - cumvalue1 end) as answer
from (select id, value1,
             (select sum(value1)
              from table t2
              where t2.id <= t.id
             ) as cumvalue1
      from table t
     ) t;

这篇关于将值减去多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！