最佳答案

ROLLUP和CUBE 用法 Oracle的GROUP BY語句除了最基本的語法外，還支持ROLLUP和CUBE語句。如果是Group by ROLLUP(A, B, C)的話，首先會對(A、B、C)進(jìn)行GROUP BY，然后對(A、B)進(jìn)行GROUP BY，然后是(A)進(jìn)行GROUP BY，最后對全表進(jìn)行GROUP BY操作。 如果是GROUP BY CUBE(A, B, C)，則首先會對(A、B、C)進(jìn)行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后對全表進(jìn)行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函數(shù)，還可以使用GROUPING_ID來標(biāo)識GROUP BY的結(jié)果。 也可以 Group by Rollup(A,(B,C)) ，Group by ARollup(B,C)，…… 這樣任意按自己想要的形式結(jié)合統(tǒng)計數(shù)據(jù)，非常方便。Rollup()：分組函數(shù)可以理解為group by的精簡模式，具體分組模式如下：　　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ù)比較一下兩個函數(shù)的不同：1、準(zhǔn)備數(shù)據(jù)：2、使用rollup函數(shù)查詢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、對比：從最后查詢出來的數(shù)據(jù)條數(shù)就差了好多，下面看一下將兩個函數(shù)從轉(zhuǎn)化成對應(yīng)的group函數(shù)語句：rollup函數(shù)：select group_id,job,name,sum(salary) from GROUP_TEST group by rollup(group_id,job,name);等價于：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) ;等價于：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、由此可見兩個函數(shù)對于匯總統(tǒng)計來說要比普通函數(shù)好用的多，另外還有一個配套使用的函數(shù)grouping(**):當(dāng)**字段為null的時候值為1，當(dāng)字段**非null的時候值為0；select grouping(group_id),job,name,sum(salary) from GROUP_TEST group by rollup(group_id,job,name);6、添加一列用來直觀的顯示所有的匯總字段：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