SQL 中 WHERE 和 HAVING 的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6360237/
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
Difference between WHERE and HAVING in SQL
提问by CuriousKen
Possible Duplicate:
SQL: What's the difference between HAVING and WHERE?
I have seen various discussions on WHEREand HAVING. I still have a question: is HAVINGused only when considering aggregates, or can it be used in more general terms: whenever you have created or aliased a field in your query?
I know that WHEREshould always be used when possible.
我看到了关于WHERE和 的各种讨论HAVING。我仍然有一个问题：HAVING仅在考虑聚合时使用，还是可以在更一般的术语中使用：每当您在查询中创建或别名字段时？我知道WHERE应该在可能的情况下始终使用它。
回答by sealz
HAVINGspecifies a search for something used in the SELECTstatement.
HAVING指定搜索SELECT语句中使用的内容。
In other words.
换句话说。
HAVINGapplies to groups.
HAVING适用于团体。
WHEREapplies to rows.
WHERE适用于行。
回答by venimus
The WHEREclause is used to restrict records, and is also used by the
query optimizer to determine which indexes and tables to use. HAVINGis a "filter" on the final resultset, and is applied after
GROUP BY, so sql cannot use it to optimize the query.
该WHERE子句用于限制记录，查询优化器也使用该子句来确定要使用哪些索引和表。 HAVING是最终结果集上的“过滤器”，并在 之后应用
GROUP BY，因此 sql 无法使用它来优化查询。
WHEREis applied for each row while extracting. HAVINGextracts all rows then filter the result.
WHERE提取时应用于每一行。HAVING提取所有行然后过滤结果。
Thus WHEREcannot be used for aggregate functions, because they require the full rowset to be extracted.
因此WHERE不能用于聚合函数，因为它们需要提取完整的行集。
回答by Asaph
HAVINGis only for conditions involving aggregates used in conjunction with the GROUP BYclause. eg. COUNT, SUM, AVG, MAX, MIN. WHEREis for any non-aggregage conditions. They can even be used together in the same query. eg.
HAVING仅用于涉及与GROUP BY子句结合使用的聚合的条件。例如。COUNT, SUM, AVG, MAX, MIN. WHERE适用于任何非聚合条件。它们甚至可以在同一个查询中一起使用。例如。
SELECT t1.id, COUNT(*) FROM table1 AS t1
INNER JOIN table2 AS t2 ON t2.t1_id = t1.id
WHERE t1.score > 50
GROUP BY t1.id HAVING COUNT(*) > 2;
Update #1:
更新 #1：
Turns out there is a non-aggregate usage of HAVINGthat I didn't know about. The query below which uses an alias only works with the HAVINGkeyword, not the WHEREkeyword. See my test in MySQL:
结果发现有一种HAVING我不知道的非聚合用法。下面使用别名的查询仅适用于HAVING关键字，而不适用于WHERE关键字。查看我在 MySQL 中的测试：
mysql> create table my_contacts (
-> id int unsigned primary key auto_increment,
-> first_name varchar(32) not null,
-> last_name varchar(32) not null,
-> index (last_name, first_name)
-> ) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into my_contacts (first_name, last_name)
-> values ('Randy', 'Hymanson'), ('Billy', 'Johnson'), ('Sam', 'Harris'), ('Lenny', 'Benson'), ('Sue', 'Flax');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT last_name AS l FROM my_contacts HAVING l LIKE '%son';
+---------+
| l |
+---------+
| Benson |
| Hymanson |
| Johnson |
+---------+
3 rows in set (0.00 sec)
mysql> SELECT last_name AS l FROM my_contacts WHERE l LIKE '%son';
ERROR 1054 (42S22): Unknown column 'l' in 'where clause'
Update #2:
更新#2：
I've now tested the novel use of HAVINGon SQL Server and it does notwork. So this may be a MySQL-only feature. Also, @Denis pointed out in the comments that this trick only works if the column/alias can be disambiguated and it only works on some engines.
现在我已经测试了新的使用HAVINGSQL Server上，它并没有正常工作。所以这可能是一个仅限 MySQL 的功能。此外，@Denis 在评论中指出，此技巧仅在列/别名可以消除歧义时才有效，并且仅适用于某些引擎。
回答by Bala R
HAVINGis used when you have a GROUP BYclause and you are trying to filter based on one of the grouping fields; WHEREis used for filtering otherwise.
HAVING当您有一个GROUP BY子句并且您尝试根据分组字段之一进行过滤时使用；WHERE否则用于过滤。
eg.
例如。
select StudentName from students where Age > 20
select className, count(studentId) from classes group by className
having count(studentId) > 10
