使用 SUM() 而不对结果进行分组

2022-01-09 00:00:00 sum mysql

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

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.

相关文章