SQL HAVING

本章将学习如何过滤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查询可以同时包含WHEREHAVING子句,但是在这种情况下,WHERE子句必须出现在GROUP BY子句之前,而HAVING子句必须出现在GROUP BY子句之后,ORDER BY子句之前。



浙ICP备17015664号 浙公网安备 33011002012336号 联系我们 网站地图  
@2019 qikegu.com 版权所有,禁止转载