我说下理解的意思 你看对不对create table tab (a number); select case when a>50 and a<100 then 1 when a>100 and a<150 then 1.5 ...(后面自己拼就行)end from tab;
select floor((&n+49)*2/100)/2 from dual;
SQL> SQL> with tab as 2 ( 3 select 20 id from dual union all 4 select 120 id from dual union all 5 select 170 id from dual union all 6 select 220 id from dual 7 ) 8 select case 9 when mod(id, 100) <= 50 then 10 floor(id / 100) + 0.5 11 else 12 floor(id / 100) + 1 13 end 14 from tab 15 ;CASEWHENMOD(ID,100)<=50THENFLO ------------------------------ 0.5 1.5 2 2.5SQL>
感谢回答 三楼正解!select floor((&n+49)*2/100)/2 from dual;结贴
scott@ORCL> ed 已写入 file afiedt.buf 1 create or replace function fun_int(cnt number) return number 2 as 3 v_num number(28,1); 4 res number; 5 begin 6 if cnt>50 then 7 v_num:=cnt/100; 8 if instr(v_num,'.')>0 then 9 select 10 decode(sign(substr(v_num,instr(v_num,'.')+1)-5),1,ceil(v_num),0,ceil(v_num), 11 substr(v_num,1,instr(v_num,'.')-1)+0.5) 12 into res from dual; 13 else 14 res:=v_num; 15 end if; 16 else 17 null; 18 end if; 19 return res; 20* end; scott@ORCL> /函数已创建。scott@ORCL> ed 已写入 file afiedt.buf 1* select fun_int(52),fun_int(126),fun_int(151),fun_int(200) from dual scott@ORCL> /FUN_INT(52) FUN_INT(126) FUN_INT(151) FUN_INT(200) ----------- ------------ ------------ ------------ 1 1.5 2 2
select case when a>50 and a<100 then 1
when a>100 and a<150 then 1.5 ...(后面自己拼就行)end
from tab;
SQL>
SQL> with tab as
2 (
3 select 20 id from dual union all
4 select 120 id from dual union all
5 select 170 id from dual union all
6 select 220 id from dual
7 )
8 select case
9 when mod(id, 100) <= 50 then
10 floor(id / 100) + 0.5
11 else
12 floor(id / 100) + 1
13 end
14 from tab
15 ;CASEWHENMOD(ID,100)<=50THENFLO
------------------------------
0.5
1.5
2
2.5SQL>
已写入 file afiedt.buf 1 create or replace function fun_int(cnt number) return number
2 as
3 v_num number(28,1);
4 res number;
5 begin
6 if cnt>50 then
7 v_num:=cnt/100;
8 if instr(v_num,'.')>0 then
9 select
10 decode(sign(substr(v_num,instr(v_num,'.')+1)-5),1,ceil(v_num),0,ceil(v_num),
11 substr(v_num,1,instr(v_num,'.')-1)+0.5)
12 into res from dual;
13 else
14 res:=v_num;
15 end if;
16 else
17 null;
18 end if;
19 return res;
20* end;
scott@ORCL> /函数已创建。scott@ORCL> ed
已写入 file afiedt.buf 1* select fun_int(52),fun_int(126),fun_int(151),fun_int(200) from dual
scott@ORCL> /FUN_INT(52) FUN_INT(126) FUN_INT(151) FUN_INT(200)
----------- ------------ ------------ ------------
1 1.5 2 2