SQL求解
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