select isnull((select sum(1) from t where 成绩>A.成绩),0)+1 as '名次',* from t A
order by 成绩 desc
1、
Select *,IsNull((Select sum(1) from t1 where 总分>a.总分),0)+1 as 名次
from t1 a
order by (Select sum(1) from t1 where 总分>=a.总分)
id name 总分 名次
----------- ---------- ----------- -----------
1 jksfff 270 1
5 sfjskj 270 1
3 skfjsk 249 3
4 sfjskj 249 3
2 loskfi 238 5
(所影响的行数为 5 行)
2、
Select *,IsNull((Select count(distinct 总分) from t1 where 总分>a.总分),0)+1 as 名次
from t1 a
order by (Select sum(1) from t1 where 总分>=a.总分)
id name 总分 名次
----------- ---------- ----------- -----------
1 jksfff 270 1
5 sfjskj 270 1
3 skfjsk 249 2
4 sfjskj 249 2
2 loskfi 238 3
(所影响的行数为 5 行)
注: oracle 里 isnull 替换为 nvl