从另一个表中的特定点开始的SQL中的SUM值 [英] SUM values in SQL starting from a specific point in another table

问题描述

我有一张列出索引/顺序，名称和值的表.例如，它看起来像这样:

  TABLE1:ID |NAME |价值1 |A |2个2 |B |53 |C |2个4 |D |75 |E |0 

现在，我还有另一个表，其中包含NAME的随机列表.它只会显示A，B，C，D或E.根据名称是什么，我想计算到达E所需的所有值的总和.这有意义吗?

因此，例如，我的表格如下所示:

  TABLE2:名称d乙一种 

我想在NAME旁边的另一列显示总和.所以D将有7，因为下一个事件是E.B必须是5、2和7的总和，因为B是5，C是2，而D是7.而A将有2的总和5、3和7等.

希望这很容易理解.

除了连接两个表并获得NAME的当前值外，我实际上没有什么其他的.但是我不确定如何增加等等，然后继续添加?

 选择T2.NAME，T1.VALUE从表1 T1左联接Table2 T2 ON T1.NAME = T2.NAME 

这是否有可能?还是我在浪费时间?我应该参考实际的代码来做到这一点吗?还是我应该做一个功能?

我不确定从哪里开始，我希望有人可以帮助我.

提前谢谢！

解决方案

正如gregory所建议的，您可以使用一个简单的窗口函数来完成此操作，在这种情况下，该函数将汇总当前行之后(包括当前行)的所有行.在 ID 值上.显然，您可以采用多种不同的方式对数据进行切片，尽管我将让您自己探索:)

---

 声明@t表(ID int，Name nvarchar(50)，Val int);插入@t值(1，'A'，2)，(2，'B'，5)，(3，'C'，2)，(4，'D'，7)，(5，'E'，0);选择ID-desc使前面的工作正确进行.这是，Name-本质上是"sum(Val)over(在当前行和后面的无限行之间按ID行排序)"的简写，Val-在功能上是相同的，但要输入更多内容...，sum(Val)超过s(按ID desc行无限制地在前面排序)为s从T按ID订购； 

哪个会输出:

  + ---- + ------ + ----- + ---- +|ID |姓名|Val |s |+ ---- + ------ + ----- + ---- +|1 |A |2 |16 ||2 |B |5 |14 ||3 |C |2 |9 ||4 |D |7 |7 ||5 |E |0 |0 |+ ---- + ------ + ----- + ---- + 

I have a table that lists the index/order, the name, and the value. For example, it looks like this:

TABLE1:
ID | NAME | VALUE
1  | A    | 2
2  | B    | 5
3  | C    | 2
4  | D    | 7
5  | E    | 0

Now, I have another table that has a random list of NAMEs. It'll just show either A, B, C, D, or E. Depending on what the NAME is, I wanted to calculate the SUM of all the values that it will take to get to E. Does that make sense?

So if for example, my table looks like this:

TABLE2:
NAME
D
B
A

I'd want another column next to NAME that'll show the sum. So D would have 7 because the next event is E. B would have to be the sum of 5, 2, and 7 because B is 5, and C is 2, and D is 7. And A would have the sum of 2, 5, 3, and 7 and so on.

Hopefully this is easy to understand.

I actually don't have much at all aside from joining the two tables and getting the current value of the NAME. But I wasn't sure how to increment and so on and keep adding?

SELECT T2.NAME, T1.VALUE
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.NAME = T2.NAME

Is doing this even possible? Or am I wasting my time? Should I be referring to actual code to do this? Or should I make a function?

I wasn't sure where to start and I was hoping someone could help me out.

Thank you in advance!

解决方案

As gregory suggests, you can do this with a simple windowed function, which (in this case) will sum up all the rows after and including the current one based on the ID value. Obviously there are a number of different ways in which you can slice your data, though I'll leave that up to you to explore :)

---

declare @t table(ID int,Name nvarchar(50),Val int);
insert into @t values(1,'A',2),(2,'B',5),(3,'C',2),(4,'D',7),(5,'E',0);

select ID      -- The desc makes the preceding work the right way. This is 
        ,Name  -- essentially shorthand for "sum(Val) over (order by ID rows between current row and unbounded following)"
        ,Val   -- which is functionally the same, but a lot more typing...
        ,sum(Val) over (order by ID desc rows unbounded preceding) as s 
from @t
order by ID;

Which will output:

+----+------+-----+----+
| ID | Name | Val | s  |
+----+------+-----+----+
|  1 | A    |   2 | 16 |
|  2 | B    |   5 | 14 |
|  3 | C    |   2 |  9 |
|  4 | D    |   7 |  7 |
|  5 | E    |   0 |  0 |
+----+------+-----+----+

这篇关于从另一个表中的特定点开始的SQL中的SUM值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！