自Hive 0.70之后，HAVING 子句可以用于對(duì)聚集結(jié)果進(jìn)行條件過(guò)濾。該用法和SQL中類(lèi)似早直，這樣我們就不需要把聚集語(yǔ)句作為子查詢來(lái)過(guò)濾相關(guān)結(jié)果。請(qǐng)看以下示例市框，
> SELECT
> gender_age.age
> FROM employee
> GROUP BY gender_age.age
> HAVING count(*)=1;
+----------------+
| gender_age.age |
+----------------+
| 27 |
| 30 |
| 35 |
| 57 |
+----------------+
4 rows selected (25.829 seconds)
> SELECT
> gender_age.age,
> count(*) as cnt -- Support use column alias in HAVING, like ORDER BY
> FROM employee
> GROUP BY gender_age.age HAVING cnt=1;
+----------------+-----+
| gender_age.age | cnt |
+----------------+-----+
| 27 | 1 |
| 30 | 1 |
| 35 | 1 |
| 57 | 1 |
+----------------+-----+
4 rows selected (25.804 seconds)
此外霞扬，HAVING子句支持針對(duì)非聚集列進(jìn)行過(guò)濾，這一功能在SQL語(yǔ)句中沒(méi)有。不過(guò)喻圃，為了更好的性能萤彩，不推薦這樣使用，推薦在WHERE子句中加入相關(guān)過(guò)濾斧拍。以下為不使用HAVING雀扶，而用子查詢的示例，
> SELECT
> a.age
> FROM (
> SELECT count(*) as cnt, gender_age.age
> FROM employee GROUP BY gender_age.age
> ) a WHERE a.cnt <= 1;
+--------+| a.age |+--------+
| 57 |
| 27 |
| 35 |
+--------+
3 rows selected (87.298 seconds)
