# GROUPING SETS

``````select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
from employee
group by name, work_space[0]
GROUPING SETS((name,work_space[0]), name, ());

// 上面语句与下面语句等效

select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
from employee
group by name, work_space[0]
UNION ALL
select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
from employee
group by name
UNION ALL
select name, work_space[0] as main_place, count(employee_id) as emp_id_cnt
from employee;
``````

# ROLLUP

``````select a, b, c from table group by a, b, c WITH ROLLUP;
// 等价于下面语句
select a, b, c from table group by a, b, c
GROUPING SETS((a,b,c),(a,b),(a),());
``````

# CUBE

``````select a, b, c from table group by a, b, c WITH ROLLUP;
// 等价于下面语句
select a, b, c from table group by a, b, c
GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),());
``````

# 聚合条件 HAVING

having用于在组内进行过滤。

``````select cid,max(price) mx from orders group by cid having mx  > 1000;
//等价于下面的子查询语句
select t.cid, t.mx from (
select cid, max(price) mx from orders group by cid
) t
where t.mx > 1000;
``````
原文作者：yanzhelee
原文地址: https://www.jianshu.com/p/9502e1d58f45
本文转自网络文章，转载此文章仅为分享知识，如有侵权，请联系博主进行删除。