SQL HAVING 语句

HAVING子句允许我们指定筛选结果中出现的由group 分组的结果的条件。

WHERE 子句在选定的列上设置条件，而 HAVING 子句在由 GROUP BY 子句创建的组上设置条件。

语法

以下代码显示了 HAVING 子句在查询中的位置。

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING 子句必须跟在查询中的 GROUP BY 子句之后，并且如果使用ORDER BY 子句，则还必须在 ORDER BY 子句之前。以下代码使用了 HAVING 子句

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

示例

CUSTOMERS 表数据记录如下。

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下示例将显示具有相同年龄的记录的条数大于或等于 2 的记录，但是仅显示age列相等的多条记录中的一条。

SQL > SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

结果如下

+----+--------+-----+---------+---------+
| ID | NAME   | AGE | ADDRESS | SALARY  |
+----+--------+-----+---------+---------+
|  2 | Khilan |  25 | Delhi   | 1500.00 |
+----+--------+-----+---------+---------+