[每日一题] OCP1z0-047 :2013-07-31 扩展GROUP BY分组――ROLLUP..............................20
来源:互联网 发布:c语言99乘法表for 编辑:程序博客网 时间:2024/05/24 01:44
转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/9674443
我们都知道group by能用来分组的子句;扩展了的group by加上rollup子句,就能为每一个分组返回一条小计录,并为全部分组返回总记录,操作如下:
gyj@OCM> WITH t AS 2 (SELECT 'A' comp,'x' cate,'o' oth,100 fee FROM dual UNION ALL 3 SELECT 'A' comp,'y' cate,'m' oth,200 fee FROM dual UNION ALL 4 SELECT 'B' comp,'x' cate,'o' oth,300 fee FROM dual UNION ALL 5 SELECT 'B' comp,'y' cate,'m' oth,100 fee FROM dual) 6 SELECT comp,cate,oth,SUM(fee) 7 FROM t 8 GROUP BY comp,ROLLUP((cate,oth));C C O SUM(FEE)- - - ----------A x o 100A y m 200A 300B x o 300B y m 100B 4006 rows selected.
答案A是说只找每行小,这个应该是grouping sets,操作如下:
gyj@OCM> WITH t AS 2 (SELECT 'A' comp,'x' cate,'o' oth,100 fee FROM dual UNION ALL 3 SELECT 'A' comp,'y' cate,'m' oth,200 fee FROM dual UNION ALL 4 SELECT 'B' comp,'x' cate,'o' oth,300 fee FROM dual UNION ALL 5 SELECT 'B' comp,'y' cate,'m' oth,100 fee FROM dual) 6 SELECT comp,cate,oth,SUM(fee) 7 FROM t 8 GROUP BY comp,GROUPING SETS((cate,oth));C C O SUM(FEE)- - - ----------A y m 200B x o 300B y m 100A x o 100
答案B是说每组的合计,肯定不对了。
答案C看到一个right toleft(从右到左),因为rollup(a,b,c)是按照这顺序的先group by a,b,c然后a,b,然后a,然后不分组,从右到左4个结果unionall。
答案D是说组合所有可能,那应该是CUBE。
gyj@OCM> WITH t AS 2 (SELECT 1 a,2 b,3 c,4 d,5 e,6 f,7g,8 h 3 FROM dual) 4 SELECT 5 decode(GROUPING(a),1,0,1) a, 6 decode(GROUPING(b),1,0,1) b, 7 decode(GROUPING(c),1,0,1) c, 8 decode(GROUPING(d),1,0,1) d, 9 decode(GROUPING(e),1,0,1) e, 10 decode(GROUPING(f),1,0,1) f, 11 decode(GROUPING(g),1,0,1) g, 12 decode(GROUPING(h),1,0,1) h 13 FROM t 14 GROUP BY CUBE(a,b,c,d,e,f,g,h); A B C D E F G H---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 1 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 1 0 0 0 0 0 1 1 0 0 0 0 0 0 1 1 1 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 1 0 0 0 0 1 0 1 0 0 0 0 0 1 0 1 1 0 0 0 0 1 1 0 0 0 0 0 0 1 1 0 1 0 0 0 0 1 1 1 0 0 0 0 0 1 1 1 1 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 1 0 0 0 1 0 0 1 0省略结果。。。。。。1 1 1 1 1 1 0 0 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1 0 1 1 1 1 1 1 1 1256 rows selected.
详细可以买本“剑破冰山---oracle开发”那书看看。
正确答案是:C
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
- [每日一题] OCP1z0-047 :2013-07-31 扩展GROUP BY分组――ROLLUP..............................20
- [每日一题] OCP1z0-047 :2013-08-20 GROUP BY扩展――GROUPING、ROLLUP.....................88
- [每日一题] OCP1z0-047 :2013-07-22 group by 子句........................................................11
- [每日一题] OCP1z0-047 :2013-07-23 CONNECT BY PRIOR层次查询.....................................12
- [每日一题] OCP1z0-047 :2013-07-25 权限――角色与对象权限.............................................14
- [每日一题] OCP1z0-047 :2013-07-29 视图――别名..........................................................18
- [每日一题] OCP1z0-047 :2013-08-02 权限―――分配系统权限............................................22
- [每日一题] OCP1z0-047 :2013-08-03 约束―――延迟约束....................................................23
- [每日一题] OCP1z0-047 :2013-08-05 SELECT语句――列的表达式.....................................25
- [每日一题] OCP1z0-047 :2013-08-06 外表部――相关描述......................................................26
- [每日一题] OCP1z0-047 :2013-08-17 EXTERNAL TABLE――加载数据 ............................56
- [每日一题] OCP1z0-047 :2013-07-20 序列MINVALUE的默认值是多少?.....................................9
- [每日一题] OCP1z0-047 :2013-07-19 Rules of Precedence――括号的使用.................8
- [每日一题] OCP1z0-047 :2013-07-21 子查询――多字段的顺序..............................................10
- [每日一题] OCP1z0-047 :2013-07-27 外部表――不能被DML和建索引...................................16
- [每日一题] OCP1z0-047 :2013-07-28 多表插入――pivoting insert(旋转插入)....................17
- [每日一题] OCP1z0-047 :2013-07-30 表连接――内联视图当作表使用....................................19
- [每日一题] OCP1z0-047 :2013-08-28 DELETE..........................................................160
- Linux C 编程之进程
- 图片放大功能
- jquery之blur()、checked、animate()
- 重新开始
- “主线程”探究,谈谈我对“主线程”的理解
- [每日一题] OCP1z0-047 :2013-07-31 扩展GROUP BY分组――ROLLUP..............................20
- jQuery自定义函数
- live555源码分析-live555大致流程
- jquery 插件开发
- Deformable Part Model的学习
- jquery遍历筛选数组的几种方法和遍历解析json对象
- 数据科学
- live555 类之间的依属关系和功能分析
- 杭电4515-小Q系列故事——世界上最遥远的距离