大数据学习之路

SQL : having

定义

  • where 子句在聚合前先筛选记录
  • having 子句在聚合后对组记录进行筛选

用法

group by 之后筛选 cnt>100 的值

mysql> select state, count(*) as cnt  from customer group by state order by cnt desc limit 10;
+----------------------+-----+
| state                | cnt |
+----------------------+-----+
| California           | 120 |
| Texas                | 113 |
| Florida              |  64 |
| New York             |  54 |
| Virginia             |  40 |
| Pennsylvania         |  35 |
| Minnesota            |  32 |
| District of Columbia |  30 |
| Georgia              |  29 |
| Michigan             |  24 |
+----------------------+-----+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select state, count(*) as cnt  from customer group by state having cnt >100 order by cnt desc ;
+------------+-----+
| state      | cnt |
+------------+-----+
| California | 120 |
| Texas      | 113 |
+------------+-----+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8

reference