HAVING
描述
根据指定条件筛选由 GROUP BY 生成的结果。 通常与 GROUP BY 子句结合使用。
语法
HAVING boolean_expression参数
boolean_expression
计算得出 BOOLEAN 结果类型的任何表达式。 使用逻辑运算符（例如 AND 或 OR）可以将两个或多个表达式组合在一起。
HAVING 子句中指定的表达式只能引用以下内容：
- 常量表达式
- GROUP BY 中显示的表达式
- 聚合函数
示例
CREATE TABLE dealer (id int, city string, car_model string, quantity int);
INSERT INTO dealer VALUES (100, 'Fremont', 'Honda Civic', 10),
(100, 'Fremont', 'Honda Accord', 15),
(100, 'Fremont', 'Honda CRV', 7),
(200, 'Dublin', 'Honda Civic', 20),
(200, 'Dublin', 'Honda Accord', 10),
(200, 'Dublin', 'Honda CRV', 3),
(300, 'San Jose', 'Honda Civic', 5),
(300, 'San Jose', 'Honda Accord', 8);
SELECT id, sum(quantity) FROM dealer GROUP BY id ORDER BY id;
+---------+-----------+
| city| sum|
+---------+-----------+
| Fremont| 32|
+---------+-----------+
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum(quantity) > 15;
+---------+-----------+
| city| sum|
+---------+-----------+
| Dublin| 33|
+---------+-----------+
| Fremont| 32|
+---------+-----------+
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum > 15;
+---------+-----------+
| city| sum|
+---------+-----------+
| Dublin| 33|
+---------+-----------+
| Fremont| 32|
+---------+-----------+
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING max(quantity) > 15;
+---------+-----------+
| city| sum|
+---------+-----------+
| Dublin| 33|
+---------+-----------+
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING 1 > 0 ORDER BY city;
+---------+-----------+
| city| sum|
+---------+-----------+
| Dublin| 33|
+---------+-----------+
| Fremont| 32|
+---------+-----------+
| San Jose| 13|
+---------+-----------+
SELECT sum(quantity) AS sum FROM dealer HAVING sum(quantity) > 10;
+---------+
| sum|
+---------+
| 78|
+---------+