如何应用SUM操作而不将结果分组到SQL中？ [英] How to apply a SUM operation without grouping the results in SQL?

问题描述

我有这样一个表：

+----+---------+----------+
| id | group   | value    |
+----+---------+----------+
|  1 | GROUP A | 0.641028 | 
|  2 | GROUP B | 0.946927 | 
|  3 | GROUP A | 0.811552 | 
|  4 | GROUP C | 0.216978 | 
|  5 | GROUP A | 0.650232 | 
+----+---------+----------+

如果我执行以下查询：

If I perform the following query:

SELECT `id`, SUM(`value`) AS `sum` FROM `test` GROUP BY `group`;

显然，我得到：

I, obviously, get:

+----+-------------------+
| id | sum               |
+----+-------------------+
|  1 |  2.10281205177307 | 
|  2 | 0.946927309036255 | 
|  4 | 0.216977506875992 | 
+----+-------------------+

但是我需要一个这样的表：

But I need a table like this one:

+----+-------------------+
| id | sum               |
+----+-------------------+
|  1 |  2.10281205177307 | 
|  2 | 0.946927309036255 | 
|  3 |  2.10281205177307 | 
|  4 | 0.216977506875992 | 
|  5 |  2.10281205177307 | 
+----+-------------------+

总结行被明确重复。

有没有办法在不使用多个（嵌套）查询的情况下获得这个结果？

Is there a way to obtain this result without using multiple (nested) queries?

推荐答案

IT将取决于您的SQL服务器，在Postgres / Oracle中我会使用窗口函数。在MySQL中......不可能afaik。

IT would depend on your SQL server, in Postgres/Oracle I'd use Window Functions. In MySQL... not possible afaik.

也许你可以像这样伪造：

Perhaps you can fake it like this:

SELECT a.id, SUM(b.value) AS `sum`
FROM test AS a
JOIN test AS b ON a.`group` = b.`group`
GROUP BY a.id, b.`group`;

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