求sql语句!排名次!

Question

我有以下成绩表
考号  科目代号 得分
0001  01       60
0001  02       80
0001  03       90
0001  04       100
0002  01       50
0002  02       40
0002  03       60
0002  04       90            如何得到以下结果考号 科目01 科目02 科目03 科目04 总分 名次
0001 60    80       90     100  330  1
0002 50    40       60     900  240  2

Answer 1

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', 90declare @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 tmpdrop table tb,tmp

Answer 2

楼上正解,动态SQL,当时就是这样的

Answer 3

应该多加如下语句
select a.总分,真名次=(select count(DISTINCT 总分 ) from tmp where 总分>=a.总分 ) from tmp a

Answer 4

select kh, sum(df) as c, sum( case dh when '01' then df end) as '01', 
sum( case dh when '02' then df end) as '02', sum( case dh when '03' then df end) as '03',
 sum( case dh when '04' then df end) as '04'
from t
group by kh
order by c desc

Answer 5

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', 180
union all select '0003' ,'04', 180declare @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 总分 into tmp from tb a group by a.考号 order by sum(a.得分) desc')
select *,名次=(select count(*)+1 from tmp where 总分>a.总分) from tmp adrop table tb,tmp

Answer 6

create table #(id int identity(1,1),kh char(4),dh char(2),df numeric(18,0))
insert into #(kh,dh,df)
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 
select kh,sum(case when dh = '01' then df end) as dh1,
sum(case when dh = '02' then df end) as dh2,
sum(case when dh = '03' then df end) as dh3,
sum(case when dh = '04' then df end) as dh4,
sum(df) as zf,
identity(int,1,1) as mc
into #1
from #
group by kh
select * from #1
drop table #
drop table #1