SQL中WHERE和HAVING的区别

发布于 2024-11-15 17:15:42 字数 373 浏览 3 评论 0原文

可能的重复：
SQL：HAVING和WHERE有什么区别？

我有看到了关于 WHERE 和 HAVING 的各种讨论。我仍然有一个问题：HAVING 仅在考虑聚合时使用，还是可以用更一般的术语使用：每当您在查询中创建或别名字段时？我知道应该尽可能使用 WHERE 。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

迷爱 2024-11-22 17:15:42

HAVING 指定搜索 SELECT 语句中使用的内容。

换句话说。

HAVING 适用于组。

WHERE 适用于行。

回复收藏 0 原文

铁轨上的流浪者 2024-11-22 17:15:42

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;

更新#1：

事实证明，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', 'Jackson'), ('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  |
| Jackson |
| 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'

更新#2：

我现在已经在 SQL Server 上测试了 HAVING 的新颖用法，但它不起作用。所以这可能是 MySQL 独有的功能。另外，@Denis 在评论中指出，此技巧仅在可以消除列/别名歧义的情况下才有效，并且仅适用于某些引擎。

HAVING is only for conditions involving aggregates used in conjunction with the GROUP BY clause. eg. COUNT, SUM, AVG, MAX, MIN. WHERE is for any non-aggregage conditions. They can even be used together in the same query. eg.

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:

Turns out there is a non-aggregate usage of HAVING that I didn't know about. The query below which uses an alias only works with the HAVING keyword, not the WHERE keyword. See my test in 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', 'Jackson'), ('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  |
| Jackson |
| 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:

I've now tested the novel use of HAVING on SQL Server and it does not work. 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.

回复收藏 0 原文

不甘平庸 2024-11-22 17:15:42

WHERE 子句用于限制记录，也被
查询优化器来确定要使用哪些索引和表。 拥有
是最终结果集上的“过滤器”，并在之后应用
GROUP BY，所以sql不能用它来优化查询。

WHERE 在提取时应用于每一行。 HAVING 提取所有行，然后过滤结果。

因此，WHERE 不能用于聚合函数，因为它们需要提取完整的行集。

回复收藏 0 原文

鼻尖触碰 2024-11-22 17:15:42

当您有一个 GROUP BY 子句并且您尝试根据分组字段之一进行过滤时，使用 HAVING ； WHERE 用于否则进行过滤。

例如。

select StudentName from students where Age > 20

select className, count(studentId) from classes group by className 
                                      having count(studentId) > 10

HAVING is used when you have a GROUP BY clause and you are trying to filter based on one of the grouping fields; WHERE is used for filtering otherwise.

eg.

select StudentName from students where Age > 20

select className, count(studentId) from classes group by className 
                                      having count(studentId) > 10

回复收藏 0 原文

~没有更多了~