学生排名存储过程

create table tb(考号 varchar(10),科目代号 varchar(10),得分 int)
insert into tb select '0001' ,'01', 60
union all select '0001' ,'02', 80
union all select '0001' ,'03', 90
union all select '0001' ,'04', 100
union all select '0002' ,'01', 50
union all select '0002' ,'02', 40
union all select '0002' ,'03', 60
union all select '0002' ,'04', 90

declare @sql varchar(8000)
set @sql='select a.考号'
select @sql=@sql+',[科目'+cast(科目代号 as varchar)+']=sum(case a.科目代号 when '''
+cast(科目代号 as varchar)+''' then a.得分 else 0 end)'
from tb group by 科目代号
exec(@sql+',sum(a.得分) as 总分,identity(int,1,1) as 名次 into tmp from tb a
group by a.考号 order by sum(a.得分) desc')
select * from tmp

drop table tb,tmp