关于 grouping sets 学习

出处：http://www.itpub.net/viewthread.php?tid=608107&extra=&page=1
今天逛论坛 发现了这个语句 但是看时版主讲的还不够清楚，因此进行了研究。
首先利用楼主给的语句创建和插入操作。完成之后 查看如下
 
运用分析函数 选择如下：
select * from (select id,area,stu_type,sum(score) score from students group by grouping sets((id,area,stu_type),(id,area),id) order by id,area,stu_type)

按照楼主的说法 grouping sets
select a, b, c, sum( d ) from t
group by grouping sets ( a, b, c )

等效于

select * from (
select a, null, null, sum( d ) from t group by a
union all
select null, b, null, sum( d ) from t group by b
union all
select null, null, c, sum( d ) from t group by c
)
这个意思 就是说 grouping sets((id,area,stu_type),(id,area),id)
相当于是对一个表进行了三次group by 并将三次的结果union all起来，既
select id,null area,null stu_tpe,sum(score) score from students group by id;
select id,area,stu_type,sum(score) score from students group by id,area,stu_type;
select id,area,null stu_type,sum(score) score from students group by id,area);
这三次查询的结果 结合起来 ，验证这一说法很简单 那就是对两者的结果集进行minus操作
select * from (
select id,null area,null stu_tpe,sum(score) score from students group by id
union all
select id,area,stu_type,sum(score) score from students group by id,area,stu_type
union all
select id,area,null stu_type,sum(score) score from students group by id,area)
minus
select * from (select id,area,stu_type,sum(score) score from students group by grouping sets((id,area,stu_type),(id,area),id) order by id,area,stu_type);

0 rows selected in 0.015 seconds.

另外还需要注意括号() 的用法
select id,area,stu_type,sum(score) score from students group by id,area,stu_type;
select id,area,stu_type,sum(score) score from students group by grouping sets(id),(area),(stu_type);
这两个语句执行出来是等效的.
假如后面的括号发生了变化
select * from (select id,area,stu_type,sum(score) score from students group by grouping sets(id,area),(stu_type));
minus
select * from
(select id,null area,stu_type,sum(score) score from students group by id,stu_type
union all
select null id ,area,stu_type,sum(score) score from students group by area,stu_type);
相当于id和stu_type  area和study_type进行了两次group by
那么假如在(id,area),(stu_type));前面再增加一个id又该如何理解呢？
(id,(id,area),(stu_type)); 这样就等效于
select * from (select id,area,stu_type,sum(score) score from students group by grouping sets(id,(id,area),(stu_type)))
minus
select * from
(select id,null area,null stu_type,sum(score) score from students group by id
union all
select id,area,null stu_type,sum(score) score from students group by id,area
union all
select null id, null area,stu_type,sum(score) score from students group by stu_type);
0 rows selected in 0.015 seconds.
但是这样一来就不好理解括号顺序的规律了 时间关系以后再慢慢研究吧，不过好在可以通过students表来进行规律的查找 这样一来也就不需要急切的掌握所谓的规律了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12216142/viewspace-580481/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12216142/viewspace-580481/