一、语法
HAVING boolean_expression
二、参数
| 子句/关键字 | 说明 |
| boolean_expression | 计算结果为布尔值的表达式。HAVING 中的表达式只能引用常量、GROUP BY 中的表达式或聚合函数 |
三、示例
— 创建测试表CREATE EXTERNAL TABLE hv_dealer (id INT, city STRING, car_model STRING, quantity INT) USING PARQUET LOCATION 'cosn://<your_cos_bucket>/test_having/hv_dealer';INSERT INTO hv_dealer VALUES (100,'Fremont','Civic',10),(100,'Fremont','Accord',15), (200,'Dublin','Civic',20),(300,'San Jose','Accord',8);– HAVING columnSELECT city, sum(quantity) AS s FROM hv_dealer GROUP BY city HAVING city = 'Fremont';– HAVING aggregateSELECT city, sum(quantity) AS s FROM hv_dealer GROUP BY city HAVING sum(quantity) > 15;– HAVING aliasSELECT city, sum(quantity) AS s FROM hv_dealer GROUP BY city HAVING s > 15;– HAVING different aggregateSELECT city, sum(quantity) AS s FROM hv_dealer GROUP BY city HAVING max(quantity) > 15;– HAVING without GROUP BYSELECT sum(quantity) AS s FROM hv_dealer HAVING sum(quantity) > 10;