group by 与group by rollup()一起用时

create table tmp1(col varchar2(10),value varchar2(10));
insert into tmp1 values ('test1','60');
insert into tmp1 values ('test2','120');
insert into tmp1 values ('test3','460');
insert into tmp1 values ('test4','225');
insert into tmp1 values ('test5','119');
insert into tmp1 values ('test6','350');
insert into tmp1 values ('test7','23');
insert into tmp1 values ('test1','120');
insert into tmp1 values ('test3','69');
insert into tmp1 values ('test4','89');
insert into tmp1 values ('test6','145');

这两个都好理解：
select col,value from tmp1 group by col,value;
select col,value from tmp1 group by rollup(col,value);
关键是这个：
select col,value from tmp1 group by (col,value), rollup by (col,value);

先说rollup().rollup(a,b)等于：group by a,b union group by a ,group by ()生成一个汇总
那么与group by 一起就相当于
select col,value from tmp1 group by col,value,col,value
union all
select col,value from tmp1 group by col,value,col,
union all
select col,value from tmp1 group by col,value;

这样就理解了吧，没理解的撞墙去。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24478467/viewspace-709650/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24478467/viewspace-709650/