首先是建表语句:
drop table test;
create table test
(
name varchar(20),
kemu varchar(20),
score number
);
insert into test values('testa','yuwen',10);
insert into test values('testa','英语',100);
insert into test values('testb','yuwen',60);
insert into test values('testb','yuwen',120);
insert into test values('testc','yuwen',40);
整数的话排序是没有问题的:
SELECT t.name ,t.score FROM test t order by t.score desc
但是核算出来比例就是小数类型的数据,根据小数来排序是不对的:
SELECT t.name,t.score,100*round(ratio_to_report(t.score) over(),4)||'%' as bili FROM test t
order by bili ;
这种既有整数既有小数的情况需要特殊处理:
SELECT t.name,t.score,100*round(ratio_to_report(t.score) over(),4)||'%' as bili FROM test t
order by to_number(regexp_substr(bili,'(((\d).\d+)|(\d+.(\d)|\d+))',1)) desc
最后的结果:
补充:
遇到有中文而且类型是varchar类型的,其中存在数字的数据也会出问题:
这种处理办法就是:
order by to_number(regexp_substr(字段名,'[0-9]*[0-9]',1))
就过是这样: