问题描述
假设我有这张桌子
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:
例如，如果我想返回所有先前现金总和大于 500 的第一行，则应返回到第 3 行
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
如何使用 mysql 语句执行此操作?
How do I do this using mysql statement?
使用 WHERE SUM(cash) >500不起作用
推荐答案
你只能在 HAVING 子句中使用聚合进行比较:
You can only use aggregates for comparison in the HAVING clause:
GROUP BY ...
HAVING SUM(cash) > 500
HAVING 子句要求您定义一个 GROUP BY 子句.
The HAVING clause requires you to define a GROUP BY clause.
要获取所有先前现金总和大于某个值的第一行，请使用:
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
因为聚合函数出现在子查询中，所以可以在 WHERE 子句中引用它的列别名.
Because the aggregate function occurs in a subquery, the column alias for it can be referenced in the WHERE clause.
这篇关于在 WHERE 子句中使用 mysql SUM()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！