Where和Having的异同
(1)where是查询返回结果之前进行过滤的
(2)having是查询返回结果之后，对结果进行过滤的
(3)在SQL中增加 HAVING 子句原因是，where关键字无法与聚合函数一起使用,having子句常跟group by一同使用，过滤分组后的数据
测试表
mysql> select * from t_order; +--------+---------+------------+------------+ | emp_no | dept_no | from_date | to_date | +--------+---------+------------+------------+ | 22744 | d006 | 1986-12-01 | 9999-01-01 | | 24007 | d005 | 1986-12-01 | 9999-01-01 | | 30970 | d005 | 1986-12-01 | 2017-03-29 | | 31112 | d002 | 1986-12-01 | 1993-12-10 | | 40983 | d005 | 1986-12-01 | 9999-01-01 | | NULL | d008 | 1986-12-01 | 1992-05-27 | | 48317 | d008 | 1986-12-01 | 1989-01-11 | | 49667 | d007 | 1986-12-01 | 9999-01-01 | | 50449 | d005 | 1986-12-01 | 9999-01-01 | | 10004 | d004 | 1986-12-01 | 9999-01-01 | +--------+---------+------------+------------+ 10 rows in set (0.00 sec)
Where后面如果提前使用列emp_no的别名aaa进行过滤的话，会报错列不存在，这是因为where字句是先对表进行过滤，才开始查询结果的
mysql> select emp_no as aaa from t_order where emp_no=22744; +-------+ | aaa | +-------+ | 22744 | +-------+ 1 row in set (0.00 sec) mysql> select emp_no as aaa from t_order where aaa=22744; ERROR 1054 (42S22): Unknown column 'aaa' in 'where clause'
Having后面可以跟列emp_no的原名或者别名aaa都可以，也可以跟group by，然后对分组后的聚合函数列进行筛选
mysql> select emp_no as aaa from t_order having emp_no=22744; +-------+ | aaa | +-------+ | 22744 | +-------+ 1 row in set (0.01 sec) mysql> select emp_no as aaa from t_order having aaa=22744; +-------+ | aaa | +-------+ | 22744 | +-------+ 1 row in set (0.00 sec) mysql> select dept_no,min(emp_no) aaa from t_order group by dept_no having aaa=31112; +---------+-------+ | dept_no | aaa | +---------+-------+ | d002 | 31112 | +---------+-------+ 1 row in set (0.00 sec)
