在 WHERE 子句中使用 mysql SUM()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3284409/
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
use mysql SUM() in a WHERE clause
提问by kamikaze_pilot
suppose I have this table
假设我有这张桌子
id | cash
1 200
2 301
3 101
4 700
and I want to return the first row in which the sum of all the previous cash is greater than a certain value:
我想返回第一行，其中所有先前现金的总和大于某个值：
So for instance, if I want to return the first row in which the sum of all the previous cash is greater than 500, is should return to row 3
因此，例如，如果我想返回所有先前现金总和大于 500 的第一行，则应返回到第 3 行
How do I do this using mysql statement?
如何使用 mysql 语句执行此操作？
using WHERE SUM(cash) > 500doesn't work
使用WHERE SUM(cash) > 500不起作用
回答by OMG Ponies
You can only use aggregates for comparison in the HAVING clause:
您只能在 HAVING 子句中使用聚合进行比较：
GROUP BY ...
HAVING SUM(cash) > 500
The HAVINGclause requires you to define a GROUP BY clause.
该HAVING子句要求您定义一个 GROUP BY 子句。
To get the first row where the sum of all the previous cash is greater than a certain value, use:
要获取所有先前现金总和大于某个值的第一行，请使用：
SELECT y.id, y.cash
FROM (SELECT t.id,
t.cash,
(SELECT SUM(x.cash)
FROM TABLE x
WHERE x.id <= t.id) AS running_total
FROM TABLE t
ORDER BY t.id) y
WHERE y.running_total > 500
ORDER BY y.id
LIMIT 1
Because the aggregate function occurs in a subquery, the column alias for it can be referenced in the WHERE clause.
因为聚合函数出现在子查询中，所以可以在 WHERE 子句中引用它的列别名。
回答by Eric Petroelje
Not tested, but I think this will be close?
未测试，但我认为这会很接近？
SELECT m1.id
FROM mytable m1
INNER JOIN mytable m2 ON m1.id < m2.id
GROUP BY m1.id
HAVING SUM(m1.cash) > 500
ORDER BY m1.id
LIMIT 1,2
The idea is to SUM up all the previous rows, get only the ones where the sum of the previous rows is > 500, then skip one and return the next one.
这个想法是对所有前面的行求和，只得到前面行的总和 > 500 的那些，然后跳过一个并返回下一个。
回答by Bill Karwin
In general, a condition in the WHEREclause of an SQL query can reference only a single row. The context of a WHEREclause is evaluated before any order has been defined by an ORDER BYclause, and there is no implicit order to an RDBMS table.
通常，WHERESQL 查询子句中的条件只能引用一行。在WHERE子句定义任何顺序之前评估子句的上下文ORDER BY，并且没有对 RDBMS 表的隐式顺序。
You can use a derived table to join each row to the group of rows with a lesser idvalue, and produce the sum of each sum group. Then test where the sum meets your criterion.
您可以使用派生表将每一行连接到具有较小id值的行组，并生成每个总和组的总和。然后测试总和在哪里符合您的标准。
CREATE TABLE MyTable ( id INT PRIMARY KEY, cash INT );
INSERT INTO MyTable (id, cash) VALUES
(1, 200), (2, 301), (3, 101), (4, 700);
SELECT s.*
FROM (
SELECT t.id, SUM(prev.cash) AS cash_sum
FROM MyTable t JOIN MyTable prev ON (t.id > prev.id)
GROUP BY t.id) AS s
WHERE s.cash_sum >= 500
ORDER BY s.id
LIMIT 1;
Output:
输出：
+----+----------+
| id | cash_sum |
+----+----------+
| 3 | 501 |
+----+----------+
回答by Robot
When using aggregate functions to filter, you must use a HAVING statement.
使用聚合函数过滤时，必须使用 HAVING 语句。
SELECT *
FROM tblMoney
HAVING Sum(CASH) > 500
