含义上来说，其实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
