1. 前言
sql having 子句通常与 group by 子句一起使用,用来过滤 group by 子句返回的分组结果集。
group by 子句通常和聚合函数(count()、sum()、avg()、min()、max() 等)一起使用来对结果进行分组,而 where 子句不能和聚合函数一起使用,所以 sql 又提供了 having 子句,以弥补了 where 子句的不足。
where 子句在分组操作之前起作用,having 子句在分组操作之后起作用。
2. 语法
having 子句的基本语法如下:
select column1, column2 from table1, table2 where [ conditions ] group by column1, column2 having [ conditions ] order by column1, column2
和 where 子句一样,having 子句后面也跟筛选条件,只有满足条件的数据才会被返回。您可以在 condition 条件中使用 >、<、= 等比较运算符,或者使用 and、or 等逻辑运算符来指定多个条件,或者使用 like、not like 等进行模糊匹配。
另外需要注意各个语句出现的顺序:
- where 子句对表进行第一次筛选,它紧跟 from 之后;
- group by 子句对 where 的筛选结果进行分组,它必须位于 where 之后;
- having 子句用来对分组的结果进行筛选,它必须位于 group by 子句之后;
- order by 子句对最终的结果集进行排序,它位于整个 sql 语句的最后。
3. 示例
有包含如下记录的 user 表:
+----+------------+-----------------+--------------------------+-------------+-----------------+ | id | username | password | email | cellphone | ip | +----+------------+-----------------+--------------------------+-------------+-----------------+ | 1 | mozhiyan | 123456 | [email protected] | 13112344444 | 113.96.109.117 | | 2 | fanhua | xyzabc | [email protected] | 15028882233 | 117.25.156.179 | | 3 | greatman | 123abc | [email protected] | 13290029028 | 125.64.104.35 | | 4 | xiaogun123 | hsdjs23ZX | [email protected] | 19036895933 | 101.37.97.51 | | 5 | my_code | 19951206 | [email protected] | 19234561234 | 103.133.176.211 | | 6 | guxiaonuan | xy232323 | [email protected] | 13409873222 | 113.96.109.117 | | 7 | coder | 20200304shengri | [email protected] | 15645990222 | 101.37.97.51 | | 8 | happy_gril | sd@@sd | [email protected] | 19090903636 | 220.181.38.148 | +----+------------+-----------------+--------------------------+-------------+-----------------+
统计同一个 IP 地址上的用户注册数,筛选出注册数大于 2 的结果集:
sql> select ip, count( username ) as total from user group by ip having total >=2;
执行结果:
+----------------+-------+ | ip | total | +----------------+-------+ | 101.37.97.51 | 2 | | 113.96.109.117 | 2 | +----------------+-------+
标签:group,--,where,44,子句,net,biancheng,having From: https://www.cnblogs.com/jiajunling/p/16649788.html