本章将学习如何过滤GROUP by子句返回的组。
根据条件过滤组
HAVING子句通常与GROUP BY子句一起使用，以指定组的筛选条件。HAVING子句必须与SELECT语句一起使用。
让我们看一下下面的employees和departments表。
employees
+--------+--------------+------------+---------+
| emp_id | emp_name | hire_date | dept_id |
+--------+--------------+------------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
+--------+--------------+------------+---------+
departments
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
现在，我们要找出那些没有员工的部门。
可以用HAVING子句与GROUP BY子句，就像这样:
SELECT t1.dept_name, count(t2.emp_id) AS total_employees
FROM departments AS t1 LEFT JOIN employees AS t2
ON t1.dept_id = t2.dept_id
GROUP BY t1.dept_name
HAVING total_employees = 0;
如果你执行上面的语句，会得到这样的输出:
+------------------+-----------------+
| dept_name | total_employees |
+------------------+-----------------+
| Customer Service | 0 |
+------------------+-----------------+
HAVING子句类似于WHERE子句，但只适用于整个组，而WHERE子句适用于单独的行。
SELECT查询可以同时包含WHERE和HAVING子句，但是在这种情况下，WHERE子句必须出现在GROUP BY子句之前，而HAVING子句必须出现在GROUP BY子句之后，ORDER BY子句之前。