学生成绩排名

表结构t2

sid      fs
1        200
2        200
3        190
4        190
5        180
......
 sid为学号,fs为学生总分

要求结果为:

sid       fs      paiming
1         200       1
2         200       1
3         190       2
4         190       2
5         180       3

－－－－－－－－－－－－－－－－－－－－－－－－

--测试数据：

create table t2 (sid int null,      fs float null)

go
insert into t2 values(1,       200)
insert into t2 values(2,       200)
insert into t2 values(3,       190)
insert into t2 values(4,       190)
insert into t2 values(5,       180)

go

－－－－－－－－－－－－－－－－－－－－－－－－

Sybase(MSSQL):

select *,(select count(distinct fs) from t2 where fs>=a.fs) as paiming
from t2 a
order by fs desc,sid

ACCESS:

select *,(select count(1) from (select distinct fs from t2) t where t.fs>=a.fs) as paiming
from t2 a
order by fs desc,sid;

注：在ACCESS中不支持count(distinct fs)