Tsql统计之二（实例详解 之rollup cube）

例一：数据准备：

 

create table #t(项目名称 varchar(10),项目年度 varchar(10),地市 varchar(10),申请单位 varchar(10),支持金额 int)
insert #t select '名称1','2004','省直'  ,'1公司',400
union all select '名称2','2004','省直'  ,'2公司',300
union all select '名称3','2004','石家庄','3公司',200
union all select '名称3','2005','石家庄','3公司',100
go

 

1 按项目年度,地市,申请单位做小计,总计的

select 项目名称,项目年度,地市,申请单位,支持金额
from( --这是实现汇总的部分(不考虑小计,总计处理部分的汇总)
 select *
  ,s1=0,s2=项目年度,s3=0,s4=地市,s5=0,s6=申请单位,s7=0 --这是排序需要的
 from #t
 union all
 --这是实现汇总的小计,总计部分
 select 项目名称=case 
   when grouping(项目年度)=1 then '总计'  --此处按各年度的总合计
   else '' end
  ,项目年度=case
   when grouping(项目年度)=0  --此处各年度中的地市合计
    and grouping(地市)=1 
   then 项目年度+'合计'
   else '' end
  ,地市=case 
   when grouping(项目年度)=0 
    and grouping(地市)=0
    and grouping(申请单位)=1
   then 地市+'合计'  --各年度各地市中的子公司合计
   else '' end
  ,申请单位=case 
   when grouping(申请单位)=0 then 申请单位+'合计' --各年度各地市中各子公司的详情支持金额
   else '' end
  ,支持金额=sum(支持金额)--group by 分组是为了这里的合计字段
  ,grouping(项目年度),项目年度 --这里及下面的是排序需要的
  ,grouping(地市),地市
  ,grouping(申请单位),申请单位
  ,grouping(申请单位)+1
 from #t
 group by 项目年度,地市,申请单位 with rollup --这是要生成小计,总计的分组字段
)a 
order by s1,s2,s3,s4,s5,s6,s7,支持金额 
--s1~s6是防止小计,总计排序错乱需要的,后面的支持金额是其他排序需要的
go

 

2只按项目年度,地市做小计,总计的,比较一下看看有什么不同了。

 

select 项目名称,项目年度,地市,申请单位,支持金额
from( --这是实现汇总的部分(不考虑小计,总计处理部分的汇总)
 select *
  ,s1=0,s2=项目年度,s3=0,s4=地市,s5=0 --这是排序需要的
 from #t
 union all
 --这是实现汇总的小计,总计部分
 select 项目名称=case 
   when grouping(项目年度)=1 then '总计'
   else '' end
  ,项目年度=case
   when grouping(项目年度)=0
    and grouping(地市)=1
   then 项目年度+'合计'
   else '' end
  ,地市=case 
   when grouping(地市)=0
   then 地市+'合计'
   else '' end
  ,''
  ,支持金额=sum(支持金额)
  ,grouping(项目年度),项目年度 --这里及下面的是排序需要的
  ,grouping(地市),地市
  ,grouping(地市)+1
 from #t
 group by 项目年度,地市 with rollup --这是要生成小计,总计的分组字段
)a 
order by s1,s2,s3,s4,s5,支持金额 --s1~s6是防止小计,总计排序错乱需要的,后面的支持金额是其他排序需要的

 

结果如下：与上面相比没有了按申请单位合计的 行数据，这是有group by决定的