简单Case表达式
select
e. first_name ,
e. last_name ,
e. department_id ,
case e. department_id
when 90 then '管理'
when 60 then '开发'
else '其他'
end as "部门"
from cps. public . employees e ;
select
count ( case e. department_id when 10 then 1 end ) dept10_count,
count ( case e. department_id when 20 then 1 end ) dept20_count,
count ( case e. department_id when 30 then 1 end ) dept30_count
from cps. public . employees e ;
select
count ( * ) filter( where e. department_id = 10 ) dept10_count,
count ( * ) filter( where e. department_id = 20 ) dept20_count,
count ( * ) filter( where e. department_id = 30 ) dept30_count
from cps. public . employees e ;
搜索Case表达式
select
e. first_name ,
e. last_name ,
case
when e. salary < 5000 then '低收入'
when e. salary between 5000 and 10000 then '中等收入'
else '高收入'
end as salarySummary
from
cps. public . employees e;
缩写函数
select 1 / nullif ( 1 , 0 ) as result;
select coalesce ( null , 1 , 2 ) as finalResult;
select
e. first_name ,
coalesce ( e. commission_pct, 0 ) as jintie
from cps. public . employees e ;
总结