關於 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/，如需轉載，請註明出處，否則將追究法律責任。