yesyesyes 的意思就是,自己输入以下信息 --创建测试环境 create table tb1 ( id1 int, ProvinceNum int, LevelNum int ) create table tb2 ( ProvinceNum int, ProvinceName varchar(10) ) create table tb3 ( LevelNum int, LevelName varchar(10) ) insert tb1 select 1,1,1 union select 2,2,2 union select 3,1,3 union select 4,2,1 union select 5,1,2 union select 6,2,3 union select 7,1,1 union select 8,2,2 union select 9,1,3 insert tb2 select 1,'北京' union select 2,'天津' insert tb3 select 1,'甲级' union select 2,'乙级' union select 3,'丙级'--测试 declare @sql varchar(8000),@tmp varchar(1000) select @sql='',@tmp='' select @sql=@sql+',['+LevelName+']=sum(case when C.LevelName='''+LevelName+''' then A.id1 else 0 end)' from tb3 group by LevelName select @tmp=@tmp+','+LevelName from tb3 group by LevelName select @tmp=stuff(@tmp,1,1,'') select @sql=' select B.ProvinceName'+@sql+'into # from tb1 A join tb2 B on A.ProvinceNum=B.ProvinceNum join tb3 C on C.LevelNum=A.LevelNum group by B.ProvinceName' select @sql=@sql+' select [省市名称]='''','+@tmp+',[合计]='''' from # where 1>2'+ ' union all'+ ' select ''合计'',sum('+replace(@tmp,',','),sum(')+'),sum('+replace(@tmp,',','+')+') from #'+ ' union all'+ ' select *,[合计]=('+replace(@tmp,',','+')+') from #'
@sql--一半是手工,别一半是动态生成
sql语句是一个一个打出来的,然后从查询分析器里copy出来
系统返回的信息是直接从查询分析器里copy出来的
--创建测试环境
create table tb1
(
id1 int,
ProvinceNum int,
LevelNum int
)
create table tb2
(
ProvinceNum int,
ProvinceName varchar(10)
)
create table tb3
(
LevelNum int,
LevelName varchar(10)
)
insert tb1
select 1,1,1 union
select 2,2,2 union
select 3,1,3 union
select 4,2,1 union
select 5,1,2 union
select 6,2,3 union
select 7,1,1 union
select 8,2,2 union
select 9,1,3 insert tb2
select 1,'北京' union
select 2,'天津'
insert tb3
select 1,'甲级' union
select 2,'乙级' union
select 3,'丙级'--测试
declare @sql varchar(8000),@tmp varchar(1000)
select @sql='',@tmp=''
select @sql=@sql+',['+LevelName+']=sum(case when C.LevelName='''+LevelName+''' then A.id1 else 0 end)'
from tb3 group by LevelName
select @tmp=@tmp+','+LevelName from tb3 group by LevelName
select @tmp=stuff(@tmp,1,1,'')
select @sql=' select B.ProvinceName'+@sql+'into # from tb1 A join tb2 B on A.ProvinceNum=B.ProvinceNum join tb3 C on C.LevelNum=A.LevelNum group by B.ProvinceName'
select @sql=@sql+' select [省市名称]='''','+@tmp+',[合计]='''' from # where 1>2'+
' union all'+
' select ''合计'',sum('+replace(@tmp,',','),sum(')+'),sum('+replace(@tmp,',','+')+') from #'+
' union all'+
' select *,[合计]=('+replace(@tmp,',','+')+') from #'
--print @sql
exec(@sql)
查询结果
--结果
/*
省市名称 丙级 甲级 乙级 合计
---------- ----------- ----------- ----------- -----------
合计 18.00 12.00 15.00 45.00
北京 12.00 8.00 5.00 25.00
天津 6.00 4.00 10.00 20.00
*/是COPY出来的