[每日一题] OCP1z0-047 :2013-08-20 GROUP BY扩展――GROUPING、ROLLUP.....................88

转载请注明出处: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


  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值