成绩排序,急,给高分!

我有一个成绩表,想生成一张新表,新表按总成绩排序,新增一列为名次。要求总成绩相同的名次相同。请问如何写SQL语句,要求语句效率高。比如表中数据如下:
xuehao xingming zongfen
0010 lixue 500
0015 zhansi 456
0014 heyao 512
0016 woshi 423
此表xuehao值唯一,希望生成新表如下:
mingci xuehao xingming zongfen
1 0014 heyao 512
2 0010 lixue 500
3 0015 zhansi 456
4 0016 woshi 423

请高手指教!
---------------------------------------------------------------

insert 新表
select 1+isnull((select count(*) from 成绩表 where zongfen>a.zongfen),0) as mingci,xuehao,xingming,zongfen from 成绩表 a order by zongfen desc

---------------------------------------------------------------

select 1+isnull((select count(*) from 成绩表 where zongfen>a.zongfen),0) as mingci,xuehao,xingming,zongfen
into 新表
from 成绩表 a order by zongfen desc

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus