SQL 将多行中的值汇总为一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13940397/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sum values from multiple rows into one row
提问by msallem
In SQL Server 2012, I have a table my_tablethat has columns state, month, ID, and sales.
在 SQL Server 2012 中，我有一个my_table包含列state, month, ID和sales.
My goal is to merge different rows that have the same state, month, IDinto one row while summing the salescolumn of these selected rows into the merged row.
我的目标是将具有相同的不同行合并state, month, ID为一行，同时将sales这些选定行的列汇总到合并行中。
For example:
例如：
state month ID sales
-------------------------------
FL June 0001 12,000
FL June 0001 6,000
FL June 0001 3,000
FL July 0001 6,000
FL July 0001 4,000
TX January 0050 1,000
MI April 0032 5,000
MI April 0032 8,000
CA April 0032 2,000
This what I am supposed to get
这是我应该得到的
state month ID sales
-------------------------------
FL June 0001 21,000
FL July 0001 10,000
TX January 0050 1,000
MI April 0032 13,000
CA April 0032 2,000
I did some research, and I found that the self join is supposed to do something similar to what I am supposed to get.
我做了一些研究，我发现 self join 应该做一些类似于我应该得到的事情。
回答by Taryn
Unless I am missing something in the requirements, why not just use an aggregate function with a GROUP BY:
除非我在要求中遗漏了一些东西，否则为什么不使用带有以下内容的聚合函数GROUP BY：
select state, month, id, sum(sales) Total
from yourtable
group by state, month, id
order by id
The result is:
结果是：
| STATE | MONTH | ID | TOTAL |
--------------------------------
| FL | July | 1 | 10000 |
| FL | June | 1 | 21000 |
| CA | April | 32 | 2000 |
| MI | April | 32 | 13000 |
| TX | January | 50 | 1000 |
回答by Francis P
Considering there should be an index on column id, this query would be a better solution:
考虑到 column 上应该有一个索引id，这个查询将是一个更好的解决方案：
select state, month, id, sum(sales) Total
from yourtable
group by id, state, month
order by id
