测试数据:
SQL> select * from sscore;
NAME SCORE
---------- -----
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 50
8 rows selected
针对以上表,需要按成绩进行排序,从而取得名次信息:
实现方法一:分析函数
SQL> select name,score,dense_rank() over(order by score desc) as rk
2 from sscore;
NAME SCORE RK
---------- ----- ----------
aa 99 1
gg 78 2
ee 78 2
dd 77 3
ff 76 4
cc 56 5
bb 56 5
ff 50 6
8 rows selected
实现方法二:普通SQL:
SQL> select a.name,
2 a.score,
3 (select count(distinct score) from sscore b where a.score <= b.score) as rk
4 from sscore a
5 order by rk asc;
NAME SCORE RK
---------- ----- ----------
aa 99 1
gg 78 2
ee 78 2
dd 77 3
ff 76 4
cc 56 5
bb 56 5
ff 50 6
8 rows selected