GROUP BY子句之后使用Having子句
可应用限定条件进行分组,以便系统仅对满足条件的组返回结果。因此,在GROUP BY子句后面包含了一个HAVING子句。HAVING类似于WHERE(唯一的差别是WHERE过滤行,HAVING过滤组)HAVING支持所有WHERE操作符。
例如,查找雇员数超过2个的部门的最高和最低薪水:
- SELECT dept ,MAX(salary) AS MAXIMUM ,MIN(salary) AS MINIMUM
- FROM STAFF
- GROUP BY dept
- HAVING COUNT(*) > 2
- ORDER BY dept
查询结果如下:
dept MAXIMUM MINIMUM
设计部 2600 2100
销售部 3500 3000
例如,查找雇员平均工资大于3000的部门的最高薪水和最低薪水:
- SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM
- FROM STAFF
- GROUP BY dept
- HAVING AVG(salary) > 3000
- ORDER BY dept
查询结果如下:
dept MAXIMUM MINIMUM
销售部 3500 3000
列出下单超过10单的用户id和下单数目:
SELECT t.user_id, count(t.order_id) as num from order_parent t
GROUP BY t.user_id HAVING num > 10;