(转)GROUPING …. WITH ROLLUP 做分组汇总 统计 (T-SQL)

自Sql Server联机帮助

USE AdventureWorks;
GO
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP;
GO

结果集在 SalesQuota 下面显示两个空值。第一个 NULL 代表从表中的这一列得到的空值组。第二个 NULL 位于 ROLLUP 操作所添加的汇总行之中。汇总行显示所有SalesQuota 组的 TotalSalesYTD 数量，并以 Grouping 列中的 1 进行指示。

下面是结果集：

SalesQuota     TotalSalesYTD        Grouping 
---------      -------------         --------
NULL           1533087.5999          0
250000.00      33461260.59           0
300000.00      9299677.9445          0
NULL           44294026.1344         1

(4 row(s) affected)

 
利用GROUPING来做分组汇总
　　
　　在用SQL时，我们经常会碰到这样的问题，要求分级求合计数，你是不是经常为怎么在一张表里分级统计而烦恼？这里我们可以用GROUPING()函数来解决该问题。
 
下面用实际的例子来说明，例子是一个行政区、单位、销售额的数据表。
　　--创建表并插入数据
　　Create Table T_SendMoney(StateCode varchar(6),DepCode varchar(6),SendMoney Money)
　　Insert Into T_SendMoney
　　Select '100001','310001',1000
　　UNION ALL
　　Select '100001','310001',2000
　　UNION ALL
　　Select '100001','310002',1500
　　UNION ALL
　　Select '100002','320001',3000
　　UNION ALL
　　Select '100002','320001',1200
　　UNION ALL
　　Select '100003','330001',1800
　　UNION ALL
　　Select '100003','330002',2100
　　UNION ALL
　　Select '100004','340001',2500
　　--按GROUPING来实现分级汇总数据
　　Select 
　　CASE WHEN GROUPING(StateCode)=1 THEN 'Total:' ELSE StateCode END as StateCode
　　,CASE WHEN GROUPING(DepCode)=1 THEN 'State Total:' ELSE DepCode END as DepCode
　　,Sum(SendMoney) AS SendMoney
　　From T_SendMoney
　　GROUP BY StateCode,DepCode WITH ROLLUP 
　　--查询结果
　　StateCode DepCode SendMoney
　　-----------------------------------------
　　100001 310001 3000.00
　　100001 310002 1500.00
　　100001 State Total: 4500.00
　　100002 320001 4200.00
　　100002 State Total: 4200.00
　　100003 330001 1800.00
　　100003 330002 2100.00
　　100003 State Total: 3900.00
　　100004 340001 2500.00
　　100004 State Total: 2500.00
　　Total: State Total: 15100.00
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
WITH 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