转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/10125129
正确答案:BD
在Oracle 8i中引入GROUPING(<列引用>)函数,被用来做为GROUPING()函数参数的表达式必须与出现在GROUPBY 子句中的表达式相匹配。包含了CUBE、ROLLUP或GROUPING SET关键字的组查询时,该函数对<列引用>相关列的聚合结果中的NULL值进行检查。例如通过写出decode(grouping(id),1,’ALLID’,id) id来检测id是否有一行由ROLLUP产生的空值,或着是否其在数据库中本身就是空值。如果这些NULL值是由本次ROLLUP查询生成的,那么返回1,否则返回0.
ROLLUP,该分组操作将生成一个结果集,此结果集除了包含正常分组之外,还包含小计(subtotal row)。ROLLUP操作字,对group by子句的各字段从右到左进行再聚合,逐个减少字段典型的总计和子计。如果是ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作(即GROUP BY())
rollup(a,b,c)
相当于
group by a,b,c union all
group by a,b union all
group by a union all
group by ()
根据题意,执行以上SQL返回如下结果:
hr@MYDB> SELECT e.department_id,e.job_id,d.location_id,sum(e.salary) total, 2 GROUPING(e.department_id) GRP_DEPT, 3 GROUPING(e.job_id) GRP_JOB, 4 GROUPING(d.location_id) GRP_LOC 5 FROM employees e JOIN departments d 6 ON e.department_id=d.department_id 7 GROUP BY ROLLUP(e.department_id,e.job_id,d.location_id); DEPARTMENT_ID JOB_ID LOCATION_ID TOTAL GRP_DEPT GRP_JOB GRP_LOC ------------- ---------- ----------- ---------- ---------- ---------- ---------- 10 AD_ASST 1700 4400 0 0 0 10 AD_ASST 4400 0 0 1 10 4400 0 1 1 20 MK_MAN 1800 13000 0 0 0 20 MK_MAN 13000 0 0 1 20 MK_REP 1800 6000 0 0 0 20 MK_REP 6000 0 0 1 20 19000 0 1 1 30 PU_MAN 1700 11000 0 0 0 30 PU_MAN 11000 0 0 1 30 PU_CLERK 1700 13900 0 0 0 30 PU_CLERK 13900 0 0 1 30 24900 0 1 1 40 HR_REP 2400 6500 0 0 0 40 HR_REP 6500 0 0 1 40 6500 0 1 1 50 ST_MAN 1500 36400 0 0 0 50 ST_MAN 36400 0 0 1 50 SH_CLERK 1500 64300 0 0 0 50 SH_CLERK 64300 0 0 1 50 ST_CLERK 1500 55700 0 0 0 50 ST_CLERK 55700 0 0 1 50 156400 0 1 1 60 IT_PROG 1400 28800 0 0 0 60 IT_PROG 28800 0 0 1 60 28800 0 1 1 70 PR_REP 2700 10000 0 0 0 70 PR_REP 10000 0 0 1 70 10000 0 1 1 80 SA_MAN 2500 61000 0 0 0 80 SA_MAN 61000 0 0 1 80 SA_REP 2500 243500 0 0 0 80 SA_REP 243500 0 0 1 80 304500 0 1 1 90 AD_VP 1700 34000 0 0 0 90 AD_VP 34000 0 0 1 90 AD_PRES 1700 24000 0 0 0 90 AD_PRES 24000 0 0 1 90 58000 0 1 1 100 FI_MGR 1700 12008 0 0 0 100 FI_MGR 12008 0 0 1 100 FI_ACCOUNT 1700 39600 0 0 0 100 FI_ACCOUNT 39600 0 0 1 100 51608 0 1 1 110 AC_MGR 1700 12008 0 0 0 110 AC_MGR 12008 0 0 1 110 AC_ACCOUNT 1700 8300 0 0 0 110 AC_ACCOUNT 8300 0 0 1 110 20308 0 1 1 684416 1 1 1 50 rows selected.
把上面的SQL改写成如下这条SQL,结果一样:
hr@MYDB> SELECT e.department_id,e.job_id,d.location_id,sum(e.salary) total, 2 GROUPING(e.department_id) GRP_DEPT, 3 GROUPING(e.job_id) GRP_JOB, 4 GROUPING(d.location_id) GRP_LOC 5 FROM employees e JOIN departments d 6 ON e.department_id=d.department_id 7 GROUP BY e.department_id,e.job_id,d.location_id 8 UNION ALL 9 SELECT e.department_id,e.job_id,null location_id,sum(e.salary) total, 10 GROUPING(e.department_id) GRP_DEPT, 11 GROUPING(e.job_id) GRP_JOB, 12 1 GRP_LOC 13 FROM employees e JOIN departments d 14 ON e.department_id=d.department_id 15 GROUP BY e.department_id,e.job_id 16 UNION ALL 17 SELECT e.department_id,null job_id,null location_id,sum(e.salary) total, 18 GROUPING(e.department_id) GRP_DEPT, 19 1 GRP_JOB, 20 1 GRP_LOC 21 FROM employees e JOIN departments d 22 ON e.department_id=d.department_id 23 GROUP BY e.department_id 24 UNION ALL 25 SELECT null department_id,null job_id,null location_id,sum(e.salary) total, 26 1 GRP_DEPT, 27 1 GRP_JOB, 28 1 GRP_LOC 29 FROM employees e JOIN departments d 30 ON e.department_id=d.department_id 31 order by 1,2; DEPARTMENT_ID JOB_ID LOCATION_ID TOTAL GRP_DEPT GRP_JOB GRP_LOC ------------- ---------- ----------- ---------- ---------- ---------- ---------- 10 AD_ASST 1700 4400 0 0 0 10 AD_ASST 4400 0 0 1 10 4400 0 1 1 20 MK_MAN 1800 13000 0 0 0 20 MK_MAN 13000 0 0 1 20 MK_REP 6000 0 0 1 20 MK_REP 1800 6000 0 0 0 20 19000 0 1 1 30 PU_CLERK 1700 13900 0 0 0 30 PU_CLERK 13900 0 0 1 30 PU_MAN 11000 0 0 1 30 PU_MAN 1700 11000 0 0 0 30 24900 0 1 1 40 HR_REP 6500 0 0 1 40 HR_REP 2400 6500 0 0 0 40 6500 0 1 1 50 SH_CLERK 64300 0 0 1 50 SH_CLERK 1500 64300 0 0 0 50 ST_CLERK 55700 0 0 1 50 ST_CLERK 1500 55700 0 0 0 50 ST_MAN 1500 36400 0 0 0 50 ST_MAN 36400 0 0 1 50 156400 0 1 1 60 IT_PROG 1400 28800 0 0 0 60 IT_PROG 28800 0 0 1 60 28800 0 1 1 70 PR_REP 2700 10000 0 0 0 70 PR_REP 10000 0 0 1 70 10000 0 1 1 80 SA_MAN 2500 61000 0 0 0 80 SA_MAN 61000 0 0 1 80 SA_REP 2500 243500 0 0 0 80 SA_REP 243500 0 0 1 80 304500 0 1 1 90 AD_PRES 24000 0 0 1 90 AD_PRES 1700 24000 0 0 0 90 AD_VP 1700 34000 0 0 0 90 AD_VP 34000 0 0 1 90 58000 0 1 1 100 FI_ACCOUNT 39600 0 0 1 100 FI_ACCOUNT 1700 39600 0 0 0 100 FI_MGR 1700 12008 0 0 0 100 FI_MGR 12008 0 0 1 100 51608 0 1 1 110 AC_ACCOUNT 1700 8300 0 0 0 110 AC_ACCOUNT 8300 0 0 1 110 AC_MGR 1700 12008 0 0 0 110 AC_MGR 12008 0 0 1 110 20308 0 1 1 684416 1 1 1 50 rows selected.
哈哈。。。不知道大家有没有看出来,我就不再解释了!
有问题大家可以上ITPUB找我:http://www.itpub.net/thread-1810748-1-1.html
QQ:252803295
学习交流QQ群:
DSI&Core Search Ⅰ 群:127149411(技术:已满)
DSI&Core Search Ⅱ 群:177089463(技术:未满)
DSI&Core Search Ⅲ 群:284596437(技术:未满)
DSI&Core Search Ⅳ 群:192136702(技术:未满)
DSI&Core Search Ⅴ 群:285030382(闲聊:未满)
MAIL:oracledba_cn@hotmail.com
BLOG: http://blog.csdn.net/guoyjoe
WEIBO:http://weibo.com/guoyJoe0218
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM