最佳答案

ROLLUP和CUBE 用法 Oracle的GROUP BY語(yǔ)句除了最基本的語(yǔ)法外，還支持ROLLUP和CUBE語(yǔ)句。如果是Group by ROLLUP(A, B, C)的話(huà)，首先會(huì)對(duì)(A、B、C)進(jìn)行GROUP BY，然后對(duì)(A、B)進(jìn)行GROUP BY，然后是(A)進(jìn)行GROUP BY，最后對(duì)全表進(jìn)行GROUP BY操作。 如果是GROUP BY CUBE(A, B, C)，則首先會(huì)對(duì)(A、B、C)進(jìn)行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后對(duì)全表進(jìn)行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函數(shù)，還可以使用GROUPING_ID來(lái)標(biāo)識(shí)GROUP BY的結(jié)果。 也可以 Group by Rollup(A,(B,C)) ，Group by ARollup(B,C)，…… 這樣任意按自己想要的形式結(jié)合統(tǒng)計(jì)數(shù)據(jù)，非常方便。Rollup()：分組函數(shù)可以理解為group by的精簡(jiǎn)模式，具體分組模式如下：　　Rollup(a,b,c): (a,b,c),(a,b),(a),(全表)Cube():分組函數(shù)也是以group by為基礎(chǔ)，具體分組模式如下：　　cube(a,b,c):(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),(全表)下面準(zhǔn)備數(shù)據(jù)比較一下兩個(gè)函數(shù)的不同：1、準(zhǔn)備數(shù)據(jù)：2、使用rollup函數(shù)查詢(xún)select group_id,job,name,sum(salary) from GROUP_TEST group by rollup(group_id,job,name)；3、使用cube函數(shù)：select group_id,job,name,sum(salary) from GROUP_TEST group by cube(group_id,job,name)4、對(duì)比：從最后查詢(xún)出來(lái)的數(shù)據(jù)條數(shù)就差了好多，下面看一下將兩個(gè)函數(shù)從轉(zhuǎn)化成對(duì)應(yīng)的group函數(shù)語(yǔ)句：rollup函數(shù)：select group_id,job,name,sum(salary) from GROUP_TEST group by rollup(group_id,job,name);等價(jià)于：select group_id,job,name,sum(salary) from GROUP_TEST group by group_id,job,nameunion allselect group_id,job,null,sum(salary) from GROUP_TEST group by group_id,jobunion allselect group_id,null,null,sum(salary) from GROUP_TEST group by group_idunion allselect null,null,null,sum(salary) from GROUP_TESTcube函數(shù)：select group_id,job,name,sum(salary) from GROUP_TEST group by cube(group_id,job,name) ;等價(jià)于：select group_id,job,name,sum(salary) from GROUP_TEST group by group_id,job,nameunion allselect group_id,job,null,sum(salary) from GROUP_TEST group by group_id,jobunion allselect group_id,null,name,sum(salary) from GROUP_TEST group by group_id,nameunion allselect group_id,null,null,sum(salary) from GROUP_TEST group by group_idunion allselect null,job,name,sum(salary) from GROUP_TEST group by job,nameunion allselect null,job,null,sum(salary) from GROUP_TEST group by jobunion allselect null,null,name,sum(salary) from GROUP_TEST group by nameunion allselect null,null,null,sum(salary) from GROUP_TEST5、由此可見(jiàn)兩個(gè)函數(shù)對(duì)于匯總統(tǒng)計(jì)來(lái)說(shuō)要比普通函數(shù)好用的多，另外還有一個(gè)配套使用的函數(shù)grouping(**):當(dāng)**字段為null的時(shí)候值為1，當(dāng)字段**非null的時(shí)候值為0；select grouping(group_id),job,name,sum(salary) from GROUP_TEST group by rollup(group_id,job,name);6、添加一列用來(lái)直觀的顯示所有的匯總字段：select group_id,job,name,case when name is null and nvl(group_id,0)=0 and job is nullthen '全表聚合'when name is null and nvl(group_id,0)=0 and job is not null then 'JOB聚合'when name is null andgrouping(group_id)=0 and job is null then 'GROUPID聚合'when name is not null and nvl(group_id,0)=0 and job is nullthen 'Name聚合'when name is not null and grouping(group_id)=0 and job is nullthen 'GROPName聚合'when name is not null and grouping(group_id)=1 and job is not nullthen 'JOBName聚合'when name isnull and grouping(group_id)=0 and job is not nullthen 'GROUPJOB聚合'else'三列匯總' end ,sum(salary) from GROUP_TEST group by cube(group_id,job,name) ;

回答者：jfdladll642016-08-30 00:00