例:
有这样一列 列名为NO 值为
NO
8.2%
2.1%
9.4%
1.7%
3.5%
7.1%
9.8%
8.1%
那么我想得到的结果是
NO 排名
8.2% 3
2.1% 7
9.4% 2
1.7% 8
3.5% 6
7.1% 5
9.8% 1
有这样一列 列名为NO 值为
NO
8.2%
2.1%
9.4%
1.7%
3.5%
7.1%
9.8%
8.1%
那么我想得到的结果是
NO 排名
8.2% 3
2.1% 7
9.4% 2
1.7% 8
3.5% 6
7.1% 5
9.8% 1
8.1% 4
--------------
SQL>
select
no
,rank()over(
order
by
to_number(rtrim(
no
,
'%'
))
desc
)
from
tb
order
by
rowid
2 ;
NO
RANK()OVER(ORDERBYTO_NUMBER(RT
---------- ------------------------------
8.2% 3
2.1% 7
9.4% 2
1.7% 8
3.5% 6
7.1% 5
9.8% 1
8.1% 4
8
rows
selected
Executed
in
0.047 seconds
----------
select no,
rank() over( order by to_number(rtrim(trim(no),'%')) desc) num
from percnt_table
order by no desc;
因为不知道你的数据类型,我就建立了一个字符型的字段
---------
select no, row_number() over(order by to_number(replace(no,'%','')) desc) px from tb