SQL报表显示分组小计和合计是怎么实现的

[小野马1209]

问题描述：报表中是否可以实现Group By 中的小计和合计
案例说明：
以下数据先是按部门DepartName 统计数量，然后再按业务中心Center 分组

Declare @t table (Center varchar(50),DepartName varchar(50) ,State varchar(50))

insert into @t
Select '营销中心','营销中心一部','中标' Union All
Select '营销中心','营销中心二部','中标' Union All
Select '设计研究院','设计一部','中标'   Union All
Select '设计研究院','设计一部','合同'   

想要显示的效果
DepartName    中标     合同
营销中心一部    1        0
营销中心二部    1        0
营销中心 小计： 2        0
设计一部        1        1
设计研究院小计：1        1
合计：          3        1

[RINK_1]

Declare @t table (Center varchar(50),DepartName varchar(50) ,State varchar(50))
 
insert into @t
Select '营销中心','营销中心一部','中标' Union All
Select '营销中心','营销中心二部','中标' Union All
Select '设计研究院','设计一部','中标'   Union All
Select '设计研究院','设计一部','合同'


select case when GROUPING(departname)=1 and GROUPING(center)<>1 then '小计'
     when GROUPING(departname)=1 and GROUPING(center)=1 then '合计'
     else departname end as departname,
sum(case when State='中标' then 1 else 0 end) as '中标',
sum(case when State='合同' then 1 else 0 end) as '合同'
from @t
group by Center,departname with cube
having not (GROUPING(center)=1 and GROUPING(departname)=0)
order by center desc,GROUPING(departname),GROUPING(center)

[zbdzjx]

大致效果，最终再整理。

with t1(Center, DepartName, State) as
(
Select '营销中心','营销中心一部','中标' Union All
Select '营销中心','营销中心二部','中标' Union All
Select '设计研究院','设计一部','中标'   Union All
Select '设计研究院','设计一部','合同' 
)
select Center, DepartName
, sum(case when State='中标' then 1 else 0 end)
, sum(case when State='合同' then 1 else 0 end) 
from t1 
group by Center, DepartName 
with ROLLUP

[RINK_1]

引用 3 楼 kaijie_wu1209 的回复:

[quote=引用 1 楼 RINK_1 的回复:]

Declare @t table (Center varchar(50),DepartName varchar(50) ,State varchar(50))
 
insert into @t
Select '营销中心','营销中心一部','中标' Union All
Select '营销中心','营销中心二部','中标' Union All
Select '设计研究院','设计一部','中标'   Union All
Select '设计研究院','设计一部','合同'


select case when GROUPING(departname)=1 and GROUPING(center)<>1 then '小计'
     when GROUPING(departname)=1 and GROUPING(center)=1 then '合计'
     else departname end as departname,
sum(case when State='中标' then 1 else 0 end) as '中标',
sum(case when State='合同' then 1 else 0 end) as '合同'
from @t
group by Center,departname with cube
having not (GROUPING(center)=1 and GROUPING(departname)=0)
order by center desc,GROUPING(departname),GROUPING(center)

能不能再请教下，GROUPING(DepartName)=1 and GROUPING(center)<>1 这段怎么理解，GROUPING自动增加的列为1，怎么理解GROUPING(center)<>1，合计数不是也是自动 呢

引用 1 楼 RINK_1 的回复:

Declare @t table (Center varchar(50),DepartName varchar(50) ,State varchar(50))
 
insert into @t
Select '营销中心','营销中心一部','中标' Union All
Select '营销中心','营销中心二部','中标' Union All
Select '设计研究院','设计一部','中标'   Union All
Select '设计研究院','设计一部','合同'


select case when GROUPING(departname)=1 and GROUPING(center)<>1 then '小计'
     when GROUPING(departname)=1 and GROUPING(center)=1 then '合计'
     else departname end as departname,
sum(case when State='中标' then 1 else 0 end) as '中标',
sum(case when State='合同' then 1 else 0 end) as '合同'
from @t
group by Center,departname with cube
having not (GROUPING(center)=1 and GROUPING(departname)=0)
order by center desc,GROUPING(departname),GROUPING(center)

再请教下，这段什么意思

having not (GROUPING(center)=1 and GROUPING(departname)=0)

[/quote] grouping的值是由with cube这个选项来生成的，具体的你可以看看下面这个链接中的描述和例子。 http://www.cnblogs.com/nikyxxx/archive/2012/11/27/2791001.html

[小野马1209]

引用 1 楼 RINK_1 的回复:

Declare @t table (Center varchar(50),DepartName varchar(50) ,State varchar(50))
 
insert into @t
Select '营销中心','营销中心一部','中标' Union All
Select '营销中心','营销中心二部','中标' Union All
Select '设计研究院','设计一部','中标'   Union All
Select '设计研究院','设计一部','合同'


select case when GROUPING(departname)=1 and GROUPING(center)<>1 then '小计'
     when GROUPING(departname)=1 and GROUPING(center)=1 then '合计'
     else departname end as departname,
sum(case when State='中标' then 1 else 0 end) as '中标',
sum(case when State='合同' then 1 else 0 end) as '合同'
from @t
group by Center,departname with cube
having not (GROUPING(center)=1 and GROUPING(departname)=0)
order by center desc,GROUPING(departname),GROUPING(center)

能不能再请教下，GROUPING(DepartName)=1 and GROUPING(center)<>1 这段怎么理解，GROUPING自动增加的列为1，怎么理解GROUPING(center)<>1，合计数不是也是自动 呢

引用 1 楼 RINK_1 的回复:

Declare @t table (Center varchar(50),DepartName varchar(50) ,State varchar(50))
 
insert into @t
Select '营销中心','营销中心一部','中标' Union All
Select '营销中心','营销中心二部','中标' Union All
Select '设计研究院','设计一部','中标'   Union All
Select '设计研究院','设计一部','合同'


select case when GROUPING(departname)=1 and GROUPING(center)<>1 then '小计'
     when GROUPING(departname)=1 and GROUPING(center)=1 then '合计'
     else departname end as departname,
sum(case when State='中标' then 1 else 0 end) as '中标',
sum(case when State='合同' then 1 else 0 end) as '合同'
from @t
group by Center,departname with cube
having not (GROUPING(center)=1 and GROUPING(departname)=0)
order by center desc,GROUPING(departname),GROUPING(center)

再请教下，这段什么意思

having not (GROUPING(center)=1 and GROUPING(departname)=0)