SQL> select * from t1;
NAME SCORE
---------- -----
AA 67
BB 85
CC 98
DD 58
EE 72
FF 85
GG 47
想得到的结果:
NAME SCORE RANK
---------- ----- ----------
CC 98 1
BB 85 1
FF 85 1
EE 72 1
AA 67 2
DD 58 3
GG 47 4
------解决方案--------------------
- SQL code
--测试表创建以及测试数据create table t_tabletwo( name varchar2(10), score number)insert into t_tabletwoselect 'AA',67 from dual union allselect 'BB',85 from dual union allselect 'CC',98 from dual union allselect 'DD',58 from dual union allselect 'EE',72 from dual union allselect 'FF',85 from dual union allselect 'GG',47 from dual --查询语句select name, score, 1 as rankfrom t_tabletwo where score>=70union allselect name, score, rownum+1 as rankfrom t_tabletwo where score <70order by score desc--查询结果CC 98 1BB 85 1FF 85 1EE 72 1AA 67 2DD 58 3GG 47 4
------解决方案--------------------
- SQL code
select t.name,t.score, dense_rank() over (order by case when score>69 then 100 else score end desc) rankfrom t_tabletwo t