在此使用oracle,oracle 中的用法为 group by [rollup|cube]( colomn),sql server中的用法为group by colomn with [rollup|cube]
首先要弄明白rollup 和cube,就要知道group by的用法,group by 为对列进行分组,只展现分组统计的值,而rollup 为分层次展现,cube为展现列中所有层次,听我娓娓道来。
1、搭建场地
首先建立一个group_by 表,列值分别为购买者名称,购买物品的大类,购买物品的小类,物品以及价格,
用户购买了一些3C以及衣物
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 create table group_by ( 2 o_name varchar2(20), 3 o_class varchar2(20), 4 o_item varchar2(20), 5 o_object varchar2(20), 6 o_price int 7 ) 8 9 insert into group_by values('springy','3C','phone','huawei',4000); 10 insert into group_by values('springy','3C','phone','xiaomi',3000); 11 insert into group_by values('springy','3C','computer','mac',10000); 12 insert into group_by values('springy','3C','phone','thinkpad',8000); 13 insert into group_by values('springy','3C','phone','huawei',4000); 14 insert into group_by values('springy','clothes','shoes','adidas',300); 15 insert into group_by values('springy','clothes','shoes','lining',400); 16 insert into group_by values('springy','clothes','pants','jackjones',500); 17 insert into group_by values('stephenson','clothes','shoes','adivon',200); 18 insert into group_by values('stephenson','clothes','shoes','nike',300); 19 insert into group_by values('stephenson','clothes','skirt','nike',300); 20 insert into group_by values('stephenson','clothes','skirt','adidas',400); 21 commit;
2、初识group by
此时向我们缓缓走来的是group by,group by 可以对数据进行分组求值。
首先对O_NAME,O_CLASS,O_ITEM进行group by 分组 ,查看购买者购买对应分类物品的价格合计。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 select o_name, o_class, o_item, sum(o_price) 2 from group_by 3 group by o_name, o_class, o_item 4 order by o_name, o_class, o_item
我们可以看到购买者没类物品消耗的价格
3、rollup
此时我们对统计的数据不太理想,除了想看每个小类,还想看每个大类,以及他们两人的消费和
表格画出来就是这个意思
表3-1
这时,我们将其与上面group by的进行对比,发现多了这些
表3-2
而这些又是怎么出来的呢?
这是就回到了开始所说的,rollup为分层次展现,怎么个分层次法呢?
首先,我们要确定,group by 的为O_NAME,O_CLASS,O_ITEM,此时对这些值进行分层次展现,即为下表
其中第一行为group by出来的,接下来的三行即为分层次汇总统计,而最后的空白的一行为对购买者进行汇总统计。
带入我们的例子中,可以看出来即为表3-1,而第2-4行则为多出来的部分,在此也可以看出来rollup 对group by中的值有顺序要求
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 select o_name,o_class,o_item,sum(o_price),grouping(o_name ),grouping(o_class),grouping(o_item) 2 from group_by 3 group by rollup (o_name,o_class,o_item) 4 order by o_name,o_class,o_item
使用rollup出来的结果
此时,看到的结果和表格也不一样呀,其中还有这么多空格怎么处理??别急,接下来会讲到
4、初始cube
说完rollup,开始说cube
开始说cube为展现所有层次,这是什么意思呢?就是有这里面所有的分组汇总统计
在此,看下我们的结果
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
select o_name,o_class,o_item,sum(o_price),grouping(o_name ),grouping(o_class),grouping(o_item) from group_by group by cube (o_name,o_class,o_item) order by o_name,o_class,o_item
5、group by、rollup、cube的用法以及区别
cube的组合
使用数字表达一下,看上去至少会更清晰点
想必这样子看上去更明白些,空值部分即该行为合计,这里面列出了所有分组。
而cube与rollup的区别在于后面对2,3部分的分组
rollup分组
group by分组
想必这样子更直观
6、处理空值
此时有一个方法为grouping() 可以查看该值是否有rollup|cube产生的,若为其值则为1,否则为0,使用一个case when判断一下就好。
下图为使用cube时,可以看出每个有cube产生合计,即空值处,其grouping的值都为1
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 select o_name,o_class,o_item,sum(o_price),grouping(o_name ),grouping(o_class),grouping(o_item) 2 from group_by 3 group by cube (o_name,o_class,o_item) 4 order by o_name,o_class,o_item
7 应用
7.1在统计时,若只有一列,那么cube和rollup没差别
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
select case when grouping(o_name) = '1' then '合计' else o_name end as customer, sum(o_price) as total, grouping(o_name) from group_by group by rollup(o_name) order by o_name
在看到上图时,只对一列进行统计时,并且有合计值时使用cube和rollup都ok。
7.2 若进行两列的统计
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 select case 2 when grouping(o_name) = '1' then 3 count(distinct o_name) || '位' 4 else 5 o_name 6 end as customer, 7 case 8 when grouping(o_class) = 1 and grouping(o_name) = 1 then 9 '合计' 10 when grouping(o_class) = 1 then 11 '小计' 12 else 13 o_class 14 end as class, 15 sum(o_price) as total, 16 grouping(o_name), 17 grouping(o_class) 18 from group_by 19 group by rollup(o_name, o_class) 20 order by o_name, o_class
看到类似上图时,总的合计中不出现第二列的详细情况时,只对两列进行总的合计时,使用rollup
7.3 两列分各种情况全统计
此时,我除了想看到两位顾客和消费,还想看到两位在各个类上的消费是
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 select case 2 when grouping(o_name) = '1' and grouping(o_class) = 1 then 3 count(distinct o_name) || '位消费' 4 when grouping(o_class) = 1 5 then 6 o_name 7 end as customer, 8 case 9 when grouping(o_class) = 1 and grouping(o_name) = 1 then 10 '合计' 11 when grouping(o_class) = 1 then 12 '小计' 13 else 14 o_class 15 end as class, 16 sum(o_price) as total, 17 grouping(o_name), 18 grouping(o_class) 19 from group_by 20 group by cube(o_name, o_class) 21 order by o_name, o_class desc
对应的图就是上面这样子。
若果仍不清楚,就挨着试一下rollup和cube。