|
含义上来说，其实having和where的意思是相同的。
但是，having处理的是聚合后的表。
而where处理的是聚合前的表。
下面我们来举个例子。
首先我们创建了一张表：
mysql> select * from salary;
+
| id | name | pay_for_per_month | position |
+
| 1 | z1 | 10000 | JAVA研发 |
| 2 | z2 | 11000 | JAVA研发 |
| 3 | z3 | 17000 | JAVA研发 |
| 4 | z4 | 18000 | c++研发 |
| 5 | z5 | 8000 | c++研发 |
| 6 | z6 | 6000 | 嵌入式开发 |
| 7 | z7 | 8000 | 嵌入式开发 |
| 8 | z8 | 9000 | 嵌入式开发 |
| 9 | z9 | 12000 | 算法工程师 |
| 10 | z10 | 15000 | 算法工程师 |
| 11 | z11 | 23000 | 算法工程师 |
| 12 | z12 | 33000 | 架构师 |
| 13 | z13 | 53000 | 架构师 |
| 14 | z14 | 153000 | 架构师 |
+
14 rows in set (0.00 sec)
然后我们来看一下having的用法：
mysql> select position,max(pay_for_per_month) from salary group by position;
+
| position | max(pay_for_per_month) |
+
| JAVA研发 | 17000 |
| c++研发 | 18000 |
| 嵌入式开发 | 9000 |
| 算法工程师 | 23000 |
| 架构师 | 153000 |
+
5 rows in set (0.00 sec)
mysql> select position,count(pay_for_per_month) from salary group by position;
+
| position | count(pay_for_per_month) |
+
| JAVA研发 | 3 |
| c++研发 | 2 |
| 嵌入式开发 | 3 |
| 算法工程师 | 3 |
| 架构师 | 3 |
+
5 rows in set (0.00 sec)
mysql> select position,max(pay_for_per_month) from salary group by position having pay_for_per_month>10000;
ERROR 1054 (42S22): Unknown column 'pay_for_per_month' in 'having clause'
mysql> select position,max(pay_for_per_month) from salary group by position having pay_for_per_month>10000;
ERROR 1054 (42S22): Unknown column 'pay_for_per_month' in 'having clause'
mysql> select position,max(pay_for_per_month) from salary group by position having max(pay_for_per_month)>10000;
+
| position | max(pay_for_per_month) |
+
| JAVA研发 | 17000 |
| c++研发 | 18000 |
| 算法工程师 | 23000 |
| 架构师 | 153000 |
+
4 rows in set (0.00 sec)
mysql> select position,count(pay_for_per_month) from salary group by position having count(id)>3;
Empty set (0.00 sec)
mysql> select position,count(pay_for_per_month) from salary group by position having count(id)>2;
+
| position | count(pay_for_per_month) |
+
| JAVA研发 | 3 |
| 嵌入式开发 | 3 |
| 算法工程师 | 3 |
| 架构师 | 3 |
+
4 rows in set (0.00 sec)
select position,pay_for_per_month from salary group by position having count(id)>2;
+
| position | pay_for_per_month |
+
| JAVA研发 | 10000 |
| 嵌入式开发 | 6000 |
| 算法工程师 | 12000 |
| 架构师 | 33000 |
+
4 rows in set (0.00 sec)
我们可以看到having后面必须跟聚合函数的内容。
下面我们来看一下where的用法
mysql> select position,pay_for_per_month from salary where position like '架构师';
+
| position | pay_for_per_month |
+
| 架构师 | 33000 |
| 架构师 | 53000 |
| 架构师 | 153000 |
+
3 rows in set (0.00 sec)
mysql> select position,pay_for_per_month from salary where position !='架构师';
+
| position | pay_for_per_month |
+
| JAVA研发 | 10000 |
| JAVA研发 | 11000 |
| JAVA研发 | 17000 |
| c++研发 | 18000 |
| c++研发 | 8000 |
| 嵌入式开发 | 6000 |
| 嵌入式开发 | 8000 |
| 嵌入式开发 | 9000 |
| 算法工程师 | 12000 |
| 算法工程师 | 15000 |
| 算法工程师 | 23000 |
+
11 rows in set (0.00 sec)
mysql> select position,pay_for_per_month from salary group by position having count(id)>2;
+
| position | pay_for_per_month |
+
| JAVA研发 | 10000 |
| 嵌入式开发 | 6000 |
| 算法工程师 | 12000 |
| 架构师 | 33000 |
+
4 rows in set (0.00 sec)
mysql> select position,max(pay_for_per_month) from salary where position !='架构师';
+
| position | max(pay_for_per_month) |
+
| JAVA研发 | 23000 |
+
1 row in set (0.00 sec)
mysql> select position,max(pay_for_per_month) from salary group by position where position !='架构 师';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where position !='架构师'' at line 1
mysql> select position,pay_for_per_month from salary group by position where position like '架构师';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where position like '架构师'' at line 1
|