Rollup、Cube,这两个函数是对查询结果集加上多维的数据信息。说的直白点,就是给查询的SQL集,加上不同的小计、合计。
Rollup、Cube,这两个函数是和group by一起使用。具体写法为:group by column_name with rollup 或 group by column_name with cube。
CUBE和 ROLLUP之间的具体区别:
· CUBE生成的结果集显示了所选列中值的所有组合的聚合。
· ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。
ROLLUP优点:
o (1)ROLLUP返回单个结果集,而 COMPUTEBY返回多个结果集,而多个结果集会增加应用程序代码的复杂性。
o (2)ROLLUP可以在服务器游标中使用,而 COMPUTEBY则不可以。
o (3)有时,查询优化器为 ROLLUP生成的执行计划比为 COMPUTEBY生成的更为高效。
下面将举例说明:
测试表为:
create table emp (
id int ,
name varchar(30),
sex varchar(2),
city varchar(30),
nation vharchar(10),
score int
)
1)、一个字段分组,即group by 后只跟一个字段
rollup用法:
select name, sum(score) from emp group by name with rollup;
等价于:
select name, sum(score) from emp group by name
union all
select null, sum(score) from emp ;
cube 用法:
select name, sum(score) from emp group by name with cube;
等价于:
select name, sum(score) from emp group by name
union all
select null, sum(score) from emp ;
2)、两个字段分组,group by 后跟2个字段
rollup 用法:
select name , sex, sum (score) from emp group by name , sex with rollup ;
等价于
select name , sex, sum (score) from emp group by name , sex
union all
select name , null, sum (score) from emp group by name
union all
select null , null, sum (score) from emp ;
cube 用法:
select name , sex, sum (score) from emp group by name , sex with cube;
等价于
select name , sex, sum (score) from emp group by name , sex with rollup
union all
select null, sex, sum (score) from emp group by sex ;
3)、三个字段分组,group by 后跟3个字段
rollup 用法:
select name , sex , nation , sum(scroe) from emp group by name , sex, nation with rollup ;
等价于
select name , sex , nation , sum(scroe) from emp group by name , sex, nation
union all
select name , sex , null, sum(scroe) from emp group by name , sex
union all
select name , null, null, sum(scroe) from emp group by name
union all
select null , null, null, sum(scroe) from emp group by sex, nation ;
cube 用法:
select name , sex , nation , sum(scroe) from emp group by name , sex, nation with cube
等价于
select name , sex , nation , sum(scroe) from emp group by name , sex, nation with rollup
union all
select null, sex , nation , sum(scroe) from emp group by sex, nation
union all
select null, null, nation , sum(scroe) from emp group by nation ;