在本教程中，我们将学习MySQL中的HAVING子句。

HAVING子句

我们将HAVING子句与GROUP BY语句一起使用以过滤结果集。
由于我们不能在GROUP BY中使用像COUNT这样的聚合函数，因此我们必须使用HAVING子句进行过滤。

HAVING语法

SELECT column_name
FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition;

示例表

在本教程中，我们将使用下面给出的"员工"，"订单"和"评论"表。

表：员工

mysql> SELECT * FROM employee;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email                | score | birthday   | lastmodified        | created             |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01        |      |   | @example.com    |     7 | 1900-01-01 | 2016-02-04 06:08:10 | 2016-01-01 01:01:01 |
| e02        | John      | Doe      | [email protected]  |     8 | 1900-02-03 | 2016-02-04 06:08:10 | 2016-01-01 01:01:04 |
| e03        | Jane      | Doe      | [email protected]  |     9 | 1900-05-20 | 2016-02-04 06:08:10 | 2016-01-01 01:01:04 |
| e04        | Tin       | Tin      | [email protected]   |     6 | 1900-10-20 | 2016-02-04 06:08:10 | 2016-01-01 01:02:03 |
| e05        | Bob       | Coder    | [email protected] |     7 | 1900-08-20 | 2016-02-04 06:08:10 | 2016-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)

表：订单

mysql> SELECT * FROM orders;
+---------+------------+--------+-------------+---------------------+---------------------+
| orderid | employeeid | amount | orderstatus | lastmodified        | created             |
+---------+------------+--------+-------------+---------------------+---------------------+
|       1 | e03        |  15.00 | OPEN        | 2016-01-02 03:04:05 | 2016-01-02 03:04:05 |
|       2 | e01        |  25.50 | OPEN        | 2016-01-04 03:04:03 | 2016-01-04 03:04:03 |
|       3 | e05        | 100.70 | CLOSED      | 2016-02-02 03:03:04 | 2016-02-02 03:03:04 |
|       4 | e02        |  22.18 | OPEN        | 2016-01-02 03:04:05 | 2016-01-02 03:04:05 |
|       5 | e04        |   9.50 | CANCELLED   | 2016-01-04 03:04:03 | 2016-01-04 03:04:03 |
|       6 | e04        |  99.99 | OPEN        | 2016-02-02 03:03:04 | 2016-02-02 03:03:04 |
+---------+------------+--------+-------------+---------------------+---------------------+
6 rows in set (0.00 sec)

表：注释

mysql> SELECT * FROM comments;
+-----------+------------+---------------------+---------------------+---------------------+
| commentid | employeeid | commentbody         | lastmodified        | created             |
+-----------+------------+---------------------+---------------------+---------------------+
|         1 | e03        | Awesome             | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 |
|         2 | e03        | Hello World         | 2016-01-02 01:02:03 | 2016-01-02 01:02:03 |
|         3 | e01        | Happy               | 2016-01-02 01:04:03 | 2016-01-02 01:04:03 |
|         4 | e04        | This is my comment. | 2016-01-01 02:03:04 | 2016-01-01 02:03:04 |
|         5 | e01        | Keep coding....     | 2016-04-04 03:02:01 | 2016-05-04 03:02:01 |
+-----------+------------+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)

例子1

在以下示例中，我们将列出每个员工下达的订单总数，但条件是该员工至少下达2个订单。

因此，首先我们将仅使用GROUP BY列出员工下达的订单总数。

mysql> SELECT 
    employeeid, COUNT(orderid) AS total_order
FROM
    orders
GROUP BY employeeid;

+------------+-------------+
| employeeid | total_order |
+------------+-------------+
| e01        |           1 |
| e02        |           1 |
| e03        |           1 |
| e04        |           2 |
| e05        |           1 |
+------------+-------------+
5 rows in set (0.00 sec)

现在，我们将获取至少下了2个订单的雇员的总订单。

mysql> SELECT 
    employeeid, COUNT(orderid) AS total_order
FROM
    orders
GROUP BY employeeid
HAVING COUNT(orderid) >= 2; 

+------------+-------------+
| employeeid | total_order |
+------------+-------------+
| e04        |           2 |
+------------+-------------+
1 row in set (0.00 sec)

我们可以看到只有一名员工e04至少下了2个订单。

示例2

在此示例中，如果雇员至少有2条评论，我们将显示其发布的雇员ID，名字，姓氏和总评论。

因此，首先我们将列出员工发布的全部评论。
为此，我们将使用GROUP BY employeeid将结果分组。

由于雇员的详细信息位于"雇员"表中，而注释的详细信息位于"评论"表中，因此，我们将把这两个表结合起来。

mysql> SELECT 
    e.employeeid,
    e.firstname,
    e.lastname,
    COUNT(c.commentid) AS total_comment
FROM
    employee e,
    comments c
WHERE
    e.employeeid = c.employeeid
GROUP BY e.employeeid;

+------------+-----------+----------+---------------+
| employeeid | firstname | lastname | total_comment |
+------------+-----------+----------+---------------+
| e01        |      |   |             2 |
| e03        | Jane      | Doe      |             2 |
| e04        | Tin       | Tin      |             1 |
+------------+-----------+----------+---------------+
3 rows in set (0.00 sec)

从上面的输出中，我们可以看到员工e01和e03发表了2条评论，而员工e04仅发表了一次评论。

注意！在上面的示例中，我们为" employee"表使用别名" e"，为" comments"表使用别名" c"。

并且我们使用列在两个表中的" employeeid"列将两个表连接在一起。

现在，我们将只列出评论至少两次的员工。

为此，我们首先将结果按" employeeid"分组，然后将hading子句应用于注释数。

SELECT 
    e.employeeid,
    e.firstname,
    e.lastname,
    COUNT(c.commentid) AS total_comment
FROM
    employee e,
    comments c
WHERE
    e.employeeid = c.employeeid
GROUP BY e.employeeid
HAVING COUNT(c.commentid) >= 2;

+------------+-----------+----------+---------------+
| employeeid | firstname | lastname | total_comment |
+------------+-----------+----------+---------------+
| e01        |      |   |             2 |
| e03        | Jane      | Doe      |             2 |
+------------+-----------+----------+---------------+
2 rows in set (0.00 sec)

我们有2名员工发表了至少2次评论。