GROUP BY中的WITH CUBE、WITH ROLLUP原理测试及GROUPING应用
前几天,看到一个群友用with rollup运算符。由于自个儿没用过,看到概念及结果都云里雾里的,所以突然来了兴趣对生成结果测了一番。
一、概念:
with cube:生成的结果集显示了所选列中值的所有组合的聚合。
with rollup:生成的结果集显示了所选列中值的某一层次结构的聚合。
grouping:当行由 with cube或with rollup运算符添加时,该函数将导致附加列的输出值为 1;当行不由 cube 或 rollup 运算符添加时,该函数将导致附加列的输出值为 0。仅在与包含 cube 或 rollup 运算符的 group by 子句相关联的选择列表中才允许分组。
二、测试:
1、建立临时表
create table #t0 ( [grade] [varchar](50) null, --年级 [class] [varchar](50) null, --班级 [name] [varchar](50) null, --姓名 [course] [varchar](50) null, --学科 [result] [numeric](8,2) null --成绩 ) create table #t1 ( [id] [int] identity(1,1) not null, --序号 [grade] [varchar](50) null, --年级 [class] [varchar](50) null, --班级 [name] [varchar](50) null, --姓名 [course] [varchar](50) null, --学科 [result] [numeric](8,2) null --成绩 ) create table #t2 ( [id] [int] identity(1,1) not null, --序号 [grade] [varchar](50) null, --年级 [class] [varchar](50) null, --班级 [name] [varchar](50) null, --姓名 [course] [varchar](50) null, --学科 [result] [numeric](8,2) null --成绩 )
2、插入测试数据
insert into #t0 (grade,class,name,course,result) select '2019','class1','9a01','c#',100 union select '2019','class1','9a02','c#',100 union select '2019','class2','9b01','c#',100 union select '2019','class2','9b02','c#',100 union select '2018','class1','8a01','java',100 union select '2018','class1','8a02','java',100 union select '2018','class2','8b01','java',100 union select '2018','class2','8b02','java',100
查询t0表结果:
3、group by
抛砖引玉,看看常用的group by排序:默认以select字段顺序(grade->class->name->course)进行排序,以下两种查询结果是一样的。
select grade,class,name,course,sum(result) result from #t0 group by grade,class,name,course select grade,class,name,course,sum(result) result from #t0 group by grade,class,name,course order by grade,class,name,course
4、with cube
原理1:以group by字段依次赋以null值进行分组聚合。
原理2:第1个字段(即grade字段)生成结果:除原始数据外,以第1个字段固定赋以null值,然后其它字段依次赋以null值进行分组聚合,结果由右往左进行排序。
下面开始测第1个字段的结果是怎么来的:
insert into #t1 (grade,class,name,course,result) select grade,class,name,course,sum(result) result from #t0 group by grade,class,name,course insert into #t1 (grade,class,name,course,result) select 'zz' grade,class,name,course,sum(result) result from #t0 group by class,name,course insert into #t1 (grade,class,name,course,result) select 'zz' grade,'zz' class,name,course,sum(result) result from #t0 group by name,course insert into #t1 (grade,class,name,course,result) select 'zz' grade,'zz' class,'zz' name,course,sum(result) result from #t0 group by course insert into #t1 (grade,class,name,course,result) select 'zz' grade,'zz' class,'zz' name,'zz' course,sum(result) result from #t0 --第1个字段结果排序由右往左 insert into #t2 (grade,class,name,course,result) select grade,class,name,course,result from #t1 where id between 1 and 27 order by course,name,class,grade update #t2 set grade=null where grade='zz' update #t2 set class=null where class='zz' update #t2 set name=null where name='zz' update #t2 set course=null where course='zz'
with cube的结果:
select grade,class,name,course,sum(result) result from #t0 group by grade,class,name,course with cube
自已测试的结果:
select * from #t2
结果与上面一致。
其它字段优先跟哪个字段组合、最终怎样排序?呃,测过,没搞清楚……
5、with rollup
原理1:除原始数据外,以group by最后1个字段(即course字段)固定赋以null值,然后其它字段依次赋以null值进行分组聚合,结果由左往右进行排序。
这个跟with cube的第1个字段非常相象:一个是第1个字段,一个是最后1个字段;一个结果是由右往左排序,一个结果是由左往右排序。
下面开始测结果是怎么来的:
truncate table #t1 truncate table #t2 insert into #t1 (grade,class,name,course,result) select grade,class,name,course,sum(result) result from #t0 group by grade,class,name,course insert into #t1 (grade,class,name,course,result) select grade,class,name,'zz' course,sum(result) result from #t0 where not exists (select 1 from #t1 where grade=#t0.grade and class=#t0.grade and name=#t0.name and course='zz') group by grade,class,name insert into #t1 (grade,class,name,course,result) select grade,class,'zz' name,'zz' course,sum(result) result from #t0 where not exists (select 1 from #t1 where grade=#t0.grade and class=#t0.class and name='zz' and course='zz') group by grade,class insert into #t1 (grade,class,name,course,result) select grade,'zz' class,'zz' name,'zz' course,sum(result) result from #t0 where not exists (select 1 from #t1 where grade=#t0.grade and class='zz' and name='zz' and course='zz') group by grade insert into #t1 (grade,class,name,course,result) select 'zz' grade,'zz' class,'zz' name,'zz' course,sum(result) result from #t0 --结果排序由左往右 insert into #t2 (grade,class,name,course,result) select grade,class,name,course,result from #t1 order by grade,class,name,course update #t2 set grade=null where grade='zz' update #t2 set class=null where class='zz' update #t2 set name=null where name='zz' update #t2 set course=null where course='zz'
with rollup的结果:
select grade,class,name,course,sum(result) result from #t0 group by grade,class,name,course with rollup
自己测试的结果:
select * from #t2
结果与上面一致。
6、grouping
这个就比较容易理解了,with cube与with rollup用法一样,先看结果:
select grade,class,name,course,sum(result) result,grouping(course) [grouping] from #t0 group by grade,class,name,course with rollup
上面grouping的是course字段,有null值就是with rollup额外添加的,grouping结果值为1。
有了grouping,那做小计、总计就方便了。
select grade, case when grouping(grade)=1 and grouping(class)=1 then '总计' when grouping(grade)=0 and grouping(class)=1 then '小计' else class end class, name,course,sum(result) result from #t0 group by grade,class,name,course with rollup
好了,原理测试及应用就到这里结束了。
上一篇: React入门学习
下一篇: ElementUI 源码定制防坑指南