关于rollup,cube,grouping sets的个人理解
来源:互联网 发布:em算法实例 编辑:程序博客网 时间:2024/04/30 13:36
1.关于group by rollup的理解
group by(字段1,字段2,字段3,字段4)
oracle先group by(字段1,字段2,字段3,字段4),group by(字段1,字段2,字段3),group by(字段1,字段2),
group by(字段1),最后对查询结果总的group by
例:
select cgicode,stockyear,stockmonth,vdc, sum(fcy*vdc) from priceadjust
where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
group by rollup(cgicode,stockyear,stockmonth,vdc) order by cgicode
1 JLY0000002 2007 9 1 0
2 JLY0000002 2007 9 0
3 JLY0000002 2007 0
4 JLY0000002 2008 8 1 0
5 JLY0000002 2008 8 0
6 JLY0000002 2008 9 1 -152
7 JLY0000002 2008 9 -152
8 JLY0000002 2008 -152
9 JLY0000002 2009 9 -1 -3121.9
10 JLY0000002 2009 9 1 8351.6
11 JLY0000002 2009 9 5229.7
12 JLY0000002 2009 5229.7
13 JLY0000002 5077.7
14 JLY0000006 2009 9 -1 -300
15 JLY0000006 2009 9 1 402.6
16 JLY0000006 2009 9 102.6
17 JLY0000006 2009 102.6
18 JLY0000006 102.6
19 5180.3
2.关于group by rollup(字段1),字段2,字段3的理解
首先以字段2,字段3分组,合计相同的字段1
后面再合计字段2,字段3
例:
select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
group by rollup(cgicode),stockyear,stockmonth order by cgicode
1 JLY0000002 2007 9 0
2 JLY0000002 2008 9 -152
3 JLY0000002 2009 9 5229.7
4 JLY0000002 2008 8 0
5 JLY0000006 2009 9 102.6
6 2009 9 5332.3
7 2008 9 -152
8 2008 8 0
9 2007 9 0
3.关于group by cube(字段1,字段2,字段3)的理解
首先group by(字段1,字段2,字段3),group by(字段1,字段2),
group by(字段1,字段3),group by(字段1),group by(字段2,字段3),
group by(字段2),group by(字段3),最后对查询结果在group by.
例:
select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
group by cube(cgicode,stockyear,stockmonth) order by cgicode
1 JLY0000002 2007 9 0
2 JLY0000002 2007 0
3 JLY0000002 2008 8 0
4 JLY0000002 2008 9 -152
5 JLY0000002 2008 -152
6 JLY0000002 2009 9 5229.7
7 JLY0000002 2009 5229.7
8 JLY0000002 8 0
9 JLY0000002 9 5077.7
10 JLY0000002 5077.7
11 JLY0000006 2009 9 102.6
12 JLY0000006 2009 102.6
13 JLY0000006 9 102.6
14 JLY0000006 102.6
15 2007 9 0
16 2007 0
17 2008 8 0
18 2008 9 -152
19 2008 -152
20 2009 9 5332.3
21 2009 5332.3
22 8 0
23 9 5180.3
24 5180.3
4.关于group by cube(字段1),字段2,字段3的理解
同2,只是排序有不同
例:
select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
group by cube(cgicode),stockyear,stockmonth order by cgicode
1 JLY0000002 2009 9 5229.7
2 JLY0000002 2007 9 0
3 JLY0000002 2008 9 -152
4 JLY0000002 2008 8 0
5 JLY0000006 2009 9 102.6
6 2009 9 5332.3
7 2008 8 0
8 2007 9 0
9 2008 9 -152
5.关于group by grouping sets(字段1,字段2,字段3)的理解
group by(字段1),group by(字段2),group by(字段3)
例如:
select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
group by grouping sets(cgicode,stockyear,stockmonth) order by cgicode
1 JLY0000002 5077.7
2 JLY0000006 102.6
3 2009 5332.3
4 2007 0
5 2008 -152
6 8 0
7 9 5180.3
6.关于group by grouping sets(字段1),字段2,字段3的理解
group by 字段1,字段2,字段3
例如:
1 JLY0000002 2007 9 0
2 JLY0000002 2008 8 0
3 JLY0000002 2008 9 -152
4 JLY0000002 2009 9 5229.7
5 JLY0000006 2009 9 102.6
7.关于group by grouping sets((字段1,字段2,字段3))的理解
注意:比5多加了一个括号
结果同6
例:
select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
group by grouping sets((cgicode,stockyear,stockmonth)) order by cgicode
1 JLY0000002 2007 9 0
2 JLY0000002 2008 8 0
3 JLY0000002 2008 9 -152
4 JLY0000002 2009 9 5229.7
5 JLY0000006 2009 9 102.6
8.关于group by grouping sets(字段1,字段2),字段3的理解
grouping by (字段1,字段3) 然后 grouping by(字段2,字段3)
例:
select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
group by grouping sets(cgicode,stockyear),stockmonth order by cgicode
1 JLY0000002 8 0
2 JLY0000002 9 5077.7
3 JLY0000006 9 102.6
4 2007 9 0
5 2008 8 0
6 2008 9 -152
7 2009 9 5332.3
9.关于group by grouping sets((字段1,字段2,字段3),())的理解
group by((字段1,字段2,字段3),然后在对查询结果进行group by
例:
select cgicode,stockyear,stockmonth,sum(fcy*vdc) from priceadjust
where (cgicode = 'JLY0000002' or cgicode = 'JLY0000006')
group by grouping sets((cgicode,stockyear,stockmonth),()) order by cgicode
1 JLY0000002 2007 9 0
2 JLY0000002 2008 8 0
3 JLY0000002 2008 9 -152
4 JLY0000002 2009 9 5229.7
5 JLY0000006 2009 9 102.6
6 5180.3
结尾语:
本文章是自己的个人理解,有什么不正之处,请读者不尽指正。
声明:
本文章可任意转载,但需注明出处。
- 关于rollup,cube,grouping sets的个人理解
- GROUPING SETS、ROLLUP、CUBE
- GROUPING SETS、ROLLUP、CUBE
- GROUPING SETS、ROLLUP、CUBE
- Oracle的rollup、cube、grouping sets函数
- Oracle的rollup、cube、grouping sets函数
- Oracle的rollup、cube、grouping sets函数
- Oracle的rollup、cube、grouping sets函数
- Oracle的rollup、cube、grouping sets函数
- Oracle的rollup、cube、grouping sets函数
- oracle 的rollup, cube, grouping sets
- Oracle的rollup、cube、grouping sets函数
- Oracle的rollup、cube、grouping sets函数
- Oracle的rollup、cube、grouping sets函数
- oracle rollup cube grouping grouping sets(转载收藏的)
- oracle提供的分析函数 cube(),rollup(),grouping sets()
- oracle提供的分析函数 cube(),rollup(),grouping sets()-----关注grouping sets用法及原理
- Oracle-->事关CUBE ROLLUP GROUPING SETS(zz)
- Java 基础知识积累
- VC实现程序自删除(三种方法)
- 浅析触摸屏板的原理及代换方法
- 数据库操作
- 随便指定一个目录,判断它是否是一个合法的JDK目录(by quqi99)
- 关于rollup,cube,grouping sets的个人理解
- 数据库死锁时紧急处理
- 这些让人哭笑不得的事
- SQL学习之Group by和Having
- 如果windows 没有IIS组件 怎么办?
- 利用RowDataBound改变gridview的列值
- test
- js中使用cf变量
- PHP连接非默认端口(1433)的sqlserver2005连接函数参数写法.