(这个的描述比较复杂,建议大家在看的时候,回到原帖去理解问题)
例如程序选择了，项目名称，项目年度，地市，申请单位，支持金额这几个字段，其中汇总字段为项目年度，地市，需要生成一个SQL语句，然后这个SQL语句查询出来的结果为：
序号 项目名称 项目年度 地市 申请单位 支持金额
1 名称1 2004 省直 1公司 100
2 名称2 2004 省直 2公司 100
3 2004 省直合计 200
4 2004合计 200
5 名称3 2005 石家庄 3公司 100
6 2005 石家庄合计 100
7 2005合计 100
8 总计 300
--以下存储过程: rea1gz
create proc proc_ROLLUP_1
@表名 varchar(30),
@统计项 varchar(300),
@统计数 varchar(300),
@不统计显示项 varchar(300),
@显示次序 varchar(300)
as
declare @临时表名 varchar(30)
set @临时表名='#'+host_name()+convert(char(8),getdate(),112)+datename(hour,getdate())+datename(minute,getdate())+datename(second,getdate())+datename(millisecond,getdate())
declare @r varchar(8000)
set @r='select '
declare @t varchar(300)
declare @x varchar(300)
declare @y varchar(300)
declare @i int
set @t=@统计项
set @i=charindex(',',@t)
if @i<=0
begin
set @r=@r+'CASE WHEN (GROUPING('
+@t+') = 1) THEN ''总计'' ELSE cast('+@t+' as varchar(30)) END as '+@t
end
else
begin
set @x=left(@t,@i-1)
set @y=right(@t,len(@t)-@i)
set @r=@r+'CASE WHEN (GROUPING('
+replace(@t,',',') = 1 and GROUPING(')
+') = 1) THEN ''总计'' WHEN (GROUPING('
+replace(@y,',',') = 1 and GROUPING(')
+') = 1) THEN cast('+@x+' as varchar(30))+''合计'''
+' ELSE cast('+@x+' as varchar(30)) END as '+@x
set @t=right(@t,len(@t)-@i)
set @i=charindex(',',@t)
while @i>0
begin
set @x=left(@t,@i-1)
set @y=right(@t,len(@t)-@i)
set @r=@r+',CASE WHEN (GROUPING('
+replace(@t,',',') = 1 and GROUPING(')
+') = 1) THEN '''' WHEN (GROUPING('
+replace(@y,',',') = 1 and GROUPING(')
+') = 1) THEN cast('+@x+' as varchar(30))+''合计'''
+' ELSE cast('+@x+' as varchar(30)) END as '+@x
set @t=right(@t,len(@t)-@i)
set @i=charindex(',',@t)
end
if len(@t)>0
begin
set @r=@r+',CASE WHEN (GROUPING('
+@t
+') = 1) THEN '''' ELSE cast('+@t+' as varchar(30)) END as '+@t
end
end
set @t=@统计数
set @i=charindex(',',@t)
if @i<=0
begin
set @r=@r+',sum('+@t+') as '+@t
end
else
begin
set @x=left(@t,@i-1)
while @i>0
begin
set @x=left(@t,@i-1)
set @r=@r+',sum('+@x+') as '+@x
set @t=right(@t,len(@t)-@i)
set @i=charindex(',',@t)
end
if len(@t)>0
set @r=@r+',sum('+@t+') as '+@t
end
set @r=@r+',cast('''' as varchar(30)) as '+replace(@不统计显示项,',',',cast('''' as varchar(30)) as ')+',2 as St'
set @r=@r+' into '+@临时表名+' FROM '+@表名+' GROUP BY '+@统计项+' WITH ROLLUP'+' insert '+@临时表名+' select '+@统计项+','+@统计数+','+@不统计显示项+',1 as st from '+@表名+' order by '+@统计项+' select IDENTITY(int,1,1) as 序号,* into #t from '+@临时表名+' order by '+@统计项+',st select 序号,'+@显示次序+' from #t order by 序号 drop table '+@临时表名+',#t'
exec (@r)
go
--调用
exec proc_ROLLUP_1 '源表','项目年度,地市','项目金额','申请单位,项目名称','项目名称,项目年度,地市,申请单位,项目金额'
----------------------------------------------------------------------------
--以下方法: zjcxc
--假设如下临时表是经过第一次汇总+排序处理得到的(第一次处理不处理小计,总计等)
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(支持金额)
,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: 只按项目年度,地市做小计,总计的,比较一下处理1,更容易看出规律
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是防止小计,总计排序错乱需要的,后面的支持金额是其他排序需要的
go
--删除测试的临时表
drop table #t
/--测试结果自己运行上央的语句来看--/