使用SUM()而不将结果分组 [英] Using SUM() without grouping the results

问题描述

我已经阅读了(本)，但找不到解决该问题的方法.我知道SUM()是一个聚合函数，不按原样使用它是没有意义的，但是在这种特定情况下，我必须在保持每一行的同时SUM()所有结果.

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.

这是桌子:

--ID-- --amount--
  1        23
  2        11
  3        8
  4        7

我需要SUM()金额，但保留每条记录，因此输出应为:

I need to SUM() the amount, but keep every record, so the output should be like:

--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

没有SUM()的情况下，它只会返回一行，但是我需要维护所有ID ...

Without the SUM() it would only return one single row, but I need to maintain all ID's...

注意:是的，这是一个非常基本的示例，我可以在这里使用php进行此操作，但是显然表更大，并且具有更多的行和列，但这不是重点.

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.

推荐答案

SELECT a.id, b.amount
FROM table1 a
CROSS JOIN
(
    SELECT SUM(amount) amount FROM table1
) b

您需要执行笛卡尔联接表中每一行与每个id的总和.由于子选择(49)只有一个结果，因此基本上只将其附加到每个id.

You need to perform a cartesian join of 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.

这篇关于使用SUM()而不将结果分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！