HAVING 子句
描述
HAVING
子句用于根据指定条件过滤由
GROUP BY
产生的结果。它通常与
GROUP BY
子句一起使用。
语法
HAVING 布尔表达式
参数
-
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);
-- `HAVING` 子句引用了 `GROUP BY` 中的列。
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING city = 'Fremont';
+-------+---+
| city|sum|
+-------+---+
|Fremont| 32|
+-------+---+
-- `HAVING` 子句引用聚合函数。
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum(quantity) > 15;
+-------+---+
| city|sum|
+-------+---+
| Dublin| 33|
|Fremont| 32|
+-------+---+
-- `HAVING` 子句通过其别名引用聚合函数。
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING sum > 15;
+-------+---+
| city|sum|
+-------+---+
| Dublin| 33|
|Fremont| 32|
+-------+---+
-- `HAVING` 子句引用不同的聚合函数，而不是出现在
-- `SELECT` 列表中的聚合函数。
SELECT city, sum(quantity) AS sum FROM dealer GROUP BY city HAVING max(quantity) > 15;
+------+---+
| city|sum|
+------+---+
|Dublin| 33|
+------+---+
-- `HAVING` 子句引用常量表达式。
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|
+--------+---+
-- `HAVING` 子句没有 `GROUP BY` 子句。
SELECT sum(quantity) AS sum FROM dealer HAVING sum(quantity) > 10;
+---+
|sum|
+---+
| 78|
+---+