sql中having和where的区别

来源：互联网发布：手写电子笔记软件编辑：程序博客网时间：2024/05/21 09:53

having后面的条件是：聚合函数的结果与所要求的值比较是否为真；

where后面的条件是：表中的字段与所要求的值比较是否为真；

例：

SELECT a.name, SUM(b.count) AS nums FROM a
INNER JOIN b
ON a.id=b.site_id
WHERE a.alexa < 200
GROUP BY a.name
HAVING SUM(b.count) > 200;

下面是选自 "a" 表的数据：

+----+--------------+---------------------------+-------+---------+| id | name         | url                       | alexa | country |+----+--------------+---------------------------+-------+---------+| 1  | Google       | https://www.google.cm/    | 1     | USA     || 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      || 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      || 4  | 微博          | http://weibo.com/         | 20    | CN      || 5  | Facebook     | https://www.facebook.com/ | 3     | USA     || 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |+----+---------------+---------------------------+-------+---------+

下面是 "b" 表的数据：
+-----+---------+-------+------------+| aid | site_id | count | date       |+-----+---------+-------+------------+|   1 |       1 |    45 | 2016-05-10 ||   2 |       3 |   100 | 2016-05-13 ||   3 |       1 |   230 | 2016-05-14 ||   4 |       2 |    10 | 2016-05-14 ||   5 |       5 |   205 | 2016-05-14 ||   6 |       4 |    13 | 2016-05-15 ||   7 |       3 |   220 | 2016-05-15 ||   8 |       5 |   545 | 2016-05-16 ||   9 |       3 |   201 | 2016-05-17 |+-----+---------+-------+------------+

0 0