create table salary (userid int,salary decimal(9,2));
-- mysql
insert into salary values(1,1000),(2,2000), (3,3000),(4,4000),(5,5000), (1,null),(2,500);
-- oracle
insert all
into salary values(1,1000)
into salary values(2,2000)
into salary values(3,3000)
into salary values(4,4000)
into salary values(5,5000)
into salary values(1,null)
into salary values(2,500)
select * from dual;
-- if(value,t f)
select salary, if(salary>2000,'high','low') hl from salary; -- oracke 不支持
-- case when ... then...else...end
select salary, case when salary<=2000 then 'low' else 'high' end hl from salary;
select salary, case when salary<=2000 then 'low' when salary is null then 'low' when salary<=4000 then 'min' else 'high' end hl from salary;
-- case ... when ... then...else...end
select salary, case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end hl from salary;