MySQL 使用 SUM() 而不对结果进行分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11357640/
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
Using SUM() without grouping the results
提问by Anonymous
I already read (this), but couldn't figure out a way to implement it to my specific problem. I know SUM()is an aggregate function and it doesn't make sense not to use it as such, but in this specific case, I have to SUM()all of the results while maintaining every single row.
我已经阅读(this)，但无法想出一种方法来实现它到我的具体问题。我知道SUM()是一个聚合函数，不使用它是没有意义的，但在这种特定情况下，我必须SUM()在维护每一行的同时获得所有结果。
Here's the table:
这是表：
--ID-- --amount--
1 23
2 11
3 8
4 7
I need to SUM()the amount, but keep every record, so the output should be like:
我需要SUM()数量，但保留每条记录，因此输出应如下所示：
--ID-- --amount--
1 49
2 49
3 49
4 49
I had this query, but it only sums each row, not all results together:
我有这个查询，但它只对每一行求和，而不是对所有结果求和：
SELECT
a.id,
SUM(b.amount)
FROM table1 as a
JOIN table1 as b ON a.id = b.id
GROUP BY id
Without the SUM()it would only return one single row, but I need to maintain all ID's...
没有SUM()它只会返回一行，但我需要维护所有的 ID ......
Note: Yes this is a pretty basic example and I could use php to do this here,but obviously the table is bigger and has more rows and columns, but that's not the point.
注意：是的，这是一个非常基本的示例，我可以在此处使用 php 来执行此操作，但显然表更大并且具有更多的行和列，但这不是重点。
采纳答案by Zane Bien
SELECT a.id, b.amount
FROM table1 a
CROSS JOIN
(
SELECT SUM(amount) amount FROM table1
) b
You need to perform a cartesian joinof the value of the sum of every row in the table to each id. Since there is only one result of the subselect (49), it basically just gets tacked onto each id.
您需要对表中每一行的总和的值执行笛卡尔连接到 each id。由于 subselect ( 49)只有一个结果，所以它基本上只是附加到每个id.
回答by Valentin Petkov
With MS SQLyou can use OVER()
有了MS SQL您可以使用 OVER()
select id, SUM(amount) OVER()
from table1;
select id, SUM(amount) OVER()
from (
select 1 as id, 23 as amount
union all
select 2 as id, 11 as amount
union all
select 3 as id, 8 as amount
union all
select 4 as id, 7 as amount
) A
--- OVER PARTITION ID
PARTITION BYwhich is very useful when you want to do SUM()per MONTH for example or do quarterly reports sales or yearly...
(Note needs distinctit is doing for all rows)
PARTITION BY例如，当您想SUM()按月执行或按季度报告销售或按年执行时，这非常有用...（注意需要distinct对所有行执行此操作）
select distinct id, SUM(amount) OVER(PARTITION BY id) as [SUM_forPARTITION]
from (
select 1 as id, 23 as amount
union all
select 1 as id, 23 as amount
union all
select 2 as id, 11 as amount
union all
select 2 as id, 11 as amount
union all
select 3 as id, 8 as amount
union all
select 4 as id, 7 as amount
) OverPARTITIONID
回答by Jon
Join the original table to the sum with a subquery:
使用子查询将原始表连接到总和：
SELECT * FROM table1, (SELECT SUM(amount) FROM table1 AS amount) t
回答by Bohemian
This does just one sum()query, so it should perform OK:
这只是一个sum()查询，所以它应该执行正常：
SELECT a.id, b.amount
FROM table1 a
cross join (SELECT SUM(amount) as amount FROM table1 AS amount) b
