SQL Server中Rollup关键字使用技巧

首先创建测试表、添加数据。

create   table  #t(a  int ,b  int ,c  int ,d  int ,e  int )
insert   into  #t  values ( 1 , 2 , 3 , 4 , 5 )
insert   into  #t  values ( 1 , 2 , 3 , 4 , 6 )
insert   into  #t  values ( 1 , 2 , 3 , 4 , 7 )
insert   into  #t  values ( 1 , 2 , 3 , 4 , 8 )
insert   into  #t  values ( 1 , 3 , 3 , 4 , 5 )
insert   into  #t  values ( 1 , 3 , 3 , 4 , 6 )
insert   into  #t  values ( 1 , 3 , 3 , 4 , 8 )
insert   into  #t  values ( 1 , 3 , 3 , 4 , 7 )

insert   into  #t  values ( 2 , 2 , 2 , 4 , 5 )
insert   into  #t  values ( 2 , 2 , 3 , 4 , 6 )
insert   into  #t  values ( 2 , 2 , 4 , 4 , 7 )
insert   into  #t  values ( 2 , 2 , 5 , 4 , 8 )
insert   into  #t  values ( 2 , 3 , 6 , 4 , 5 )
insert   into  #t  values ( 2 , 3 , 3 , 4 , 6 )
insert   into  #t  values ( 2 , 3 , 3 , 4 , 8 )
insert   into  #t  values ( 2 , 3 , 3 , 4 , 7 )

情况一：只有一个分类汇总列时，只需要一个合计。只需要增加with rollup即可。

select   case   when   grouping (a) = 1   then   ' 合计 '   else   cast (a  as   varchar )  end  a,
sum (b), sum (c), sum (d), sum (e)  from  #t  group   by  a  with  rollup

情况二:有多个分类汇总列，只需要一个合计.增加rollup之后，需要增加判断。

select   case   when   grouping (a) = 1   then   ' 合计 '   else   cast (a  as   varchar )  end  a,
    b,
sum (c), sum (d), sum (e)  from  #t 
group   by  a,b  with  rollup 
having   grouping (b) = 0   or   grouping (a) = 1

 

select   case   when   grouping (a) = 1   then   ' 合计 '   else   cast (a  as   varchar )  end  a,
    b,
    c,
sum (d), sum (e)  from  #t 
group   by  a,b,c  with  rollup 
having   grouping (c) = 0   or   grouping (a) = 1

情况三：有多个分类汇总列，需要全部的小计和合计。

select   case   when   grouping (a) = 1   then   ' 合计 '   else   cast (a  as   varchar )  end  a,
     case   when   grouping (b) = 1   and   grouping (a) = 0   then   ' 小计 '   else   cast (b  as   varchar )  end  b,
     case   when   grouping (c) = 1   and   grouping (b) = 0   then   ' 小计 '   else   cast (c  as   varchar )  end  c,
sum (d), sum (e)  from  #t 
group   by  a,b,c  with  rollup 

另外一种显示小计的方式

select   case   when   grouping (a) = 1   then   ' 合计 '  
     when   grouping (b) = 1   then   cast (a  as   varchar ) + ' 小计 '
     else   cast (a  as   varchar )  end  a,
     case   when   grouping (b) = 0   and   grouping (c) = 1  
     then   cast (b  as   varchar ) + ' 小计 '   else   cast (b  as   varchar )  end  b,
     case   when   grouping (c) = 1   and   grouping (b) = 0  
     then   ''   else   cast (c  as   varchar )  end  c,
sum (d), sum (e)  from  #t 
group   by  a,b,c  with  rollup 

情况四：有多个分类汇总列，需要部分的小计和合计

select   case   when   grouping (a) = 1   then   ' 合计 '   else   cast (a  as   varchar )  end  a,
    b,
     case   when   grouping (c) = 1   and   grouping (b) = 0   then   ' 小计 '   else   cast (c  as   varchar )  end  c,
sum (d), sum (e)  from  #t 
group   by  a,b,c  with  rollup 
having   grouping (a) = 1   or   grouping (b) = 0

 

select   case   when   grouping (a) = 1   then   ' 合计 '   else   cast (a  as   varchar )  end  a,
     case   when   grouping (b) = 1   and   grouping (a) = 0   then   ' 小计 '   else   cast (b  as   varchar )  end  b,
    c,
sum (d), sum (e)  from  #t 
group   by  a,b,c  with  rollup 
having   grouping (a) = 1   or   grouping (b) = 1   or   grouping (c) = 0