declare @s1 varchar(8000),@s2 varchar(8000) select @s1='',@s2='' select @s1=@s1+',['+sth+']=sum(case sth when '''+sth+''' then stdf end)' ,@s2=@s2+',['+sth+']=cast(avg(case sth when '''+sth+''' then stdf*1.0 end) as decimal(10,2))' from csybb group by sth exec(' select * from( select ybbh as 样本编号'+@s1+',总分=sum(stdf) from csybb group by ybbh union all select ''平均分'''+@s2+',cast(sum(stdf*1.0)/count(distinct ybbh) as decimal(10,2)) from csybb )a order by case 样本编号 when ''平均分'' then 1 end,总分')
--测试--测试数据 create table csybb(ybbh char(4),sth varchar(10),stdf int) insert csybb select '0001',1,10 union all select '0001',2,10 union all select '0001',3,6 union all select '0002',1,4 union all select '0002',2,9 union all select '0002',3,5 union all select '0003',1,1 union all select '0003',2,2 union all select '0003',3,3 go--查询处理 declare @s1 varchar(8000),@s2 varchar(8000) select @s1='',@s2='' select @s1=@s1+',['+sth+']=sum(case sth when '''+sth+''' then stdf end)' ,@s2=@s2+',['+sth+']=cast(avg(case sth when '''+sth+''' then stdf*1.0 end) as decimal(10,2))' from csybb group by sth exec(' select * from( select ybbh as 样本编号'+@s1+',总分=sum(stdf) from csybb group by ybbh union all select ''平均分'''+@s2+',cast(sum(stdf*1.0)/count(distinct ybbh) as decimal(10,2)) from csybb )a order by case 样本编号 when ''平均分'' then 1 end,总分') go--删除测试 drop table csybb/*--测试结果样本编号 1 2 3 总分 ------ -------------- -------------- -------------- -------------- 0003 1.00 2.00 3.00 6.00 0002 4.00 9.00 5.00 18.00 0001 10.00 10.00 6.00 26.00 平均分 5.00 7.00 4.67 16.67 --*/
倒排的话,在最后的总分后面加上: desc
declare @s1 varchar(8000),@s2 varchar(8000) select @s1='',@s2='' select @s1=@s1+',['+sth+']=sum(case sth when '''+sth+''' then stdf end)' ,@s2=@s2+',['+sth+']=cast(avg(case sth when '''+sth+''' then stdf*1.0 end) as decimal(10,2))' from csybb group by sth exec(' create view 视图名 as select top 100 percent * from( select ybbh as 样本编号'+@s1+',总分=sum(stdf) from csybb group by ybbh union all select ''平均分'''+@s2+',cast(sum(stdf*1.0)/count(distinct ybbh) as decimal(10,2)) from csybb )a order by case 样本编号 when ''平均分'' then 1 end,总分') go--然后 select * from 视图名
select @s1='',@s2=''
select @s1=@s1+',['+sth+']=sum(case sth when '''+sth+''' then stdf end)'
,@s2=@s2+',['+sth+']=cast(avg(case sth when '''+sth+''' then stdf*1.0 end) as decimal(10,2))'
from csybb
group by sth
exec('
select * from(
select ybbh as 样本编号'+@s1+',总分=sum(stdf)
from csybb group by ybbh
union all
select ''平均分'''+@s2+',cast(sum(stdf*1.0)/count(distinct ybbh) as decimal(10,2))
from csybb
)a order by case 样本编号 when ''平均分'' then 1 end,总分')
create table csybb(ybbh char(4),sth varchar(10),stdf int)
insert csybb select '0001',1,10
union all select '0001',2,10
union all select '0001',3,6
union all select '0002',1,4
union all select '0002',2,9
union all select '0002',3,5
union all select '0003',1,1
union all select '0003',2,2
union all select '0003',3,3
go--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
select @s1='',@s2=''
select @s1=@s1+',['+sth+']=sum(case sth when '''+sth+''' then stdf end)'
,@s2=@s2+',['+sth+']=cast(avg(case sth when '''+sth+''' then stdf*1.0 end) as decimal(10,2))'
from csybb
group by sth
exec('
select * from(
select ybbh as 样本编号'+@s1+',总分=sum(stdf)
from csybb group by ybbh
union all
select ''平均分'''+@s2+',cast(sum(stdf*1.0)/count(distinct ybbh) as decimal(10,2))
from csybb
)a order by case 样本编号 when ''平均分'' then 1 end,总分')
go--删除测试
drop table csybb/*--测试结果样本编号 1 2 3 总分
------ -------------- -------------- -------------- --------------
0003 1.00 2.00 3.00 6.00
0002 4.00 9.00 5.00 18.00
0001 10.00 10.00 6.00 26.00
平均分 5.00 7.00 4.67 16.67
--*/
select @s1='',@s2=''
select @s1=@s1+',['+sth+']=sum(case sth when '''+sth+''' then stdf end)'
,@s2=@s2+',['+sth+']=cast(avg(case sth when '''+sth+''' then stdf*1.0 end) as decimal(10,2))'
from csybb
group by sth
exec('
create view 视图名
as
select top 100 percent * from(
select ybbh as 样本编号'+@s1+',总分=sum(stdf)
from csybb group by ybbh
union all
select ''平均分'''+@s2+',cast(sum(stdf*1.0)/count(distinct ybbh) as decimal(10,2))
from csybb
)a order by case 样本编号 when ''平均分'' then 1 end,总分')
go--然后
select * from 视图名