文章来源:http://www.cnblogs.com/oneword/archive/2009/04/28/1445198.html
CUBE运算符生成的结果集是多维数据集,多维数据集是事实数据的扩展,事实数据即记录个别时间的数据,扩展建立在用户准备分析的列上,这些列被称为维,多维数据集是一个结果集,其中包含各纬度所有可能的交叉表格.
CUBE运算符是在Select语句的group by子句中指定的,group by应指定维度列和关键字with cube,结果集将包括维度列中各值的所有可能组合.
示例1.
Sql语句如下:
select * from student
select sex,sclass,sum(score) as 合计
from student
group by sex,sclass with cube
select sex,sclass,sum(score) as 合计
from student
group by sclass,sex with cube
Sql查询时这样运行:
1. 查询到性别的第一个性别为男,则先查询男生,然后分班级
2. 查询完成之后,对性别为Sex为男的数据进行合计
3. 查询性别为女的数据,查询完成之后同样也进行合计
4. 不分性别、班级进行合计汇总
5. 以上均是以性别为组来分类,因为至此时关于性别的所有汇总都已经完成
6. 按照sclass进行分组汇总.
注意:
1. 分类依据并不是根据select 中的顺序,而是根据group by中的顺序.
2. 尽量按照使select和group by中的字段顺序一致,这样在显示起来看着更舒服,具体情况具体分析.
对于上述查询的结果,我们可以看出,数据中存在空置问题,绑定到GridView后显示如下:
此中效果并没有达到能够满足实际项目中的需要,所以,我们对Sql语句应进行改进.
使用Grouping区分空值.
如何区分使用CUBE之后产生的空值和实际查询中得到的空值.这个问题可以用grouping函数来解决.如果列中的值来来自查询数据,则grouping返回0,如果列中的值是cube产生的空值,则返回1
示例2.
Sql如下:
select case when(grouping(sex)=1) then '小记' else sex
end as 性别,
case when(grouping(sclass)=1) then '小记' else sclass
end as 班级,
sum(score)
from student
group by sex,sclass with cube
在页面上显示时如下:
CUBE可以生成n维的多维数据集,即具有任意维目的多维数据集,只有一个维度的多维数据集可用于生成合计.
示例3:
SQL:
select case when(grouping(sex)=1) then '合计' else sex end as 性别,
sum(score) as 合计
from student
group by sex with cube
生成许多维度的数据集合结果可能很大,办法就是生成一个大的视图,选择显示即可.
文章来源:http://www.cnblogs.com/oneword/archive/2009/04/28/1445201.html
在生成包含小记和合计的报表时,ROLLUP运算符很有用,ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集.
ROLLUP和CUBE的区别在于:
1. cube生成的结果集显示了所选列的所有组合的聚合
2. rollup生成的结果集显示了所选列中值的某一个层次结构的聚合
示例:
Sql:
With rollup:
select case when(grouping(sex)=1) then '合计' else sex end,
case when(grouping(sclass)=1) then '合计' else sclass end,
sum(score) '合计'
from student
group by sex,sclass with rollup
With cube
select case when(grouping(sex)=1) then '小记' else sex
end as 性别,
case when(grouping(sclass)=1) then '小记' else sclass
end as 班级,
sum(score)
from student
group by sex,sclass with cube
区别解释:
1. cube操作为所选的列的所有组合做了汇总, 男/2,男/3,男/小记,女/2,女/3,女/小记,小记/小记,小记/2,小记/3
2. rollup操作并不针对多列中的所有可能进行汇总,而是以左边的列为主,列出右边的所有可能,然后汇总,不会针对右边的列,将左边列的所有汇总可能计算出来.
优点:
1. rollup返回单个结果集,而compute by返回多个结果集,多个结果集会增加代码的复杂性
2. rollup可以在服务器游标中使用,compute by不可以
3. rollup比compute by 执行起来更加高效
通过排序,可以实现系统想要的效果,
可以将小记汇总放在最上面,也可以将小记汇总放在各个分组之上.
例如:
select case when(grouping(sex)=1) then '合计' else sex end,
case when(grouping(sclass)=1) then '合计' else sclass end,
sum(score) '合计'
from student
group by sex,sclass with rollup
order by grouping(sex) desc,grouping(sclass) desc,sex
select case when(grouping(sex)=1) then '合计' else sex end,
case when(grouping(sclass)=1) then '合计' else sclass end,
sum(score) '合计'
from student
group by sex,sclass with rollup
order by grouping(sex) desc,sex,grouping(sclass) desc