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

     好了，原理测试及应用就到这里结束了。