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
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
when score<60 then '4'
when score between 50 and 60 then '3'
when score between 60 and 70 then '2'
else '1'
end
from t1;
--测试表创建以及测试数据
create table t_tabletwo
(
name varchar2(10),
score number
)insert into t_tabletwo
select 'AA',67 from dual union all
select 'BB',85 from dual union all
select 'CC',98 from dual union all
select 'DD',58 from dual union all
select 'EE',72 from dual union all
select 'FF',85 from dual union all
select 'GG',47 from dual
--查询语句
select name,
score,
1 as rank
from t_tabletwo
where score>=70
union all
select name,
score,
rownum+1 as rank
from t_tabletwo
where score <70
order by score desc
--查询结果
CC 98 1
BB 85 1
FF 85 1
EE 72 1
AA 67 2
DD 58 3
GG 47 4
我的本意是能不能用上rank() over(order by col_xx desc)
或者
dense_rank() over(order by col_xx desc)
来解决.
试了几遍都不行,估计只有你这种写法了.
select t.name,t.score,
dense_rank()
over (order by case when score>69 then 100 else score end desc) rank
from t_tabletwo t
case
when xtlsh is null then 0
when xtlsh<4000 then 1
when xtlsh<6000 then 2
else 3
end rank
from s_biz_entity order by ywst_id desc
类似于这样?
--可以用到rank() over()
--我给7楼我自己发的精进一下:
select t.name,t.score,
(case
when rank()
over (order by case when score>69 then 100 else score end desc)-(select count(*) from t_tablefour)>=1
then rank()
over (order by case when score>69 then 100 else score end desc)-(select count(*)-1 from t_tablefour)
else rank()
over (order by case when score>69 then 100 else score end desc)
end) rank
from t_tabletwo t
--数据结果
BB 85 1
CC 98 1
FF 85 1
EE 72 1
AA 67 2
DD 58 3
GG 47 4
when score<60 then '4'
when score between 50 and 60 then '3'
when score between 60 and 70 then '2'
else '1'
end
from t1;