使用聚合函数在 where 语句中小于或大于的 MySQL 查询？

Question

我有一个 super 简单的 MySQL 查询，但有一个问题，想知道是否有人可以解释一下。我想要做的就是包括两个聚合函数，它们同时添加“今年”+“去年”过滤掉所有少于 200 total_votes 的结果

目前，查询有效，输出如下所示:

name   | total_votes
--------------------
apple  |    119
lemon  |    218
orange |    201
pear   |    111

---

但是，当我添加 where 语句时，出现语法错误:

 select 
        name,
        sum(this_year)+sum(last_year) as total_votes
        from fruit_sales
        group by name
        where total_votes>200

以上导致我的 SQL fiddle 出现语法错误:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where total>200' at line 6"

我也试过:

select 
name,
sum(this_year)+sum(last_year)>200 as total_votes
from fruit_sales
group by name

这是一个带有表的 SQLfiddle 和我的查询:

http://sqlfiddle.com/#!9/a6862/11

如有任何帮助，我们将不胜感激!

Answer 1

SELECT
    name,
    SUM(this_year)+SUM(last_year) as total_votes
 FROM fruit_sales
 GROUP BY name
 HAVING SUM(this_year)+SUM(last_year) > 200

SqlFiddleDemo

您还可以计算总和:

SELECT
    name,
    SUM(this_year + last_year) as total_votes
FROM fruit_sales
GROUP BY name
HAVING total_votes > 200;

SqlFiddleDemo2

对于没有 HAVING 和使用子查询的 @lcm:

SELECT *
FROM (
SELECT
    name,
    SUM(this_year + last_year) as total_votes
 FROM fruit_sales
 GROUP BY name) AS sub
WHERE total_votes > 200;

SqlFiddleDemo3