with t as( select 0.1232 bfb from dual union all select 0.2341 from dual union all select 0.2341 from dual union all select 0.6755 from dual union all select 0.2345 from dual )select bfb*100||'%',100-2*trunc((0.9000-bfb)/0.1) from tBFB*100||'%' FS ----------------------------------------- ---------- 12.32% 86 23.41% 88 23.41% 88 67.55% 96 23.45% 88
create table t1 (col1 varchar(10));insert into t1 values ('86.22%'); insert into t1 values ('90.00%'); insert into t1 values ('33.33%'); insert into t1 values ('88.99%'); insert into t1 values ('85.11%'); insert into t1 values ('77.77%');select col1,case when 100-(900-trunc(substr(col1,1,length(col1)-1),1)*10)*2 >=0 then 100-(900-trunc(substr(col1,1,length(col1)-1),1)*10)*2 else 0 end score from t1 col1 score ----------------------------- 1 86.22% 24 2 90.00% 100 3 33.33% 0 4 88.99% 78 5 85.11% 2 6 77.77% 0
剩下的可以用sql(case when) 或者(if elsif 来写)
with t as(
select 0.1232 bfb from dual
union all
select 0.2341 from dual
union all
select 0.2341 from dual
union all
select 0.6755 from dual
union all
select 0.2345 from dual
)select bfb*100||'%',100-2*trunc((0.9000-bfb)/0.1) from tBFB*100||'%' FS
----------------------------------------- ----------
12.32% 86
23.41% 88
23.41% 88
67.55% 96
23.45% 88
create table t1 (col1 varchar(10));insert into t1 values ('86.22%');
insert into t1 values ('90.00%');
insert into t1 values ('33.33%');
insert into t1 values ('88.99%');
insert into t1 values ('85.11%');
insert into t1 values ('77.77%');select col1,case when 100-(900-trunc(substr(col1,1,length(col1)-1),1)*10)*2 >=0 then 100-(900-trunc(substr(col1,1,length(col1)-1),1)*10)*2 else 0 end score
from t1
col1 score
-----------------------------
1 86.22% 24
2 90.00% 100
3 33.33% 0
4 88.99% 78
5 85.11% 2
6 77.77% 0