**@. 显示姓名、薪水,佣金(commission),佣金为空的,
–统一加上0.05;其余的加上0.03,按照薪资降序、变更后佣金升序排列**
–1 CASE END
select
e.first_name || ‘.’ || e.last_name “姓名”,
e.salary ,
e.commission_pct,
case
when e.commission_pct is NULL then 0.05
else commission_pct+ 0.03
end “new_commission”
from
hr.employees e
order by 3 desc,4 ;
–2.,DECODE
select
e.first_name || ‘.’ || e.last_name “姓名”,
e.salary ,
e.commission_pct,
DECODE (e.commission_pct,NULL,0.05,e.commission_pct+0.03) “new_commission”
from
hr.employees e
order by 3 desc,4 ;
–3,NVL2()
select
e.first_name || ‘.’ || e.last_name “姓名”,
e.salary ,
e.commission_pct,
NVL2(e.commission_pct,e.commission_pct+0.03,0.05) “new_commission”
from
hr.employees e
order by 3 desc,4 ;