理解case表达式
1基本形式:
case
when
when
else
end
2如有下数据
id name department
3 william silverman 1
4 walt welinski 1
1 carry grant 2
2 michael lancer 2
5 sally fields 3
6 walt frazier 3
7 melissa gomez 4
如果想把部门号转为实际的字符串,可使用以下语句
select id,name,case department
when 1 then 'marketing'
when 2 then 'customer service'
when 3 then 'collictions'
when 4 then 'customer relations'
end as dept_name
from employees order by dept_name
用搜索的case表达式为:
select id,name,case when department=1 then 'marketing'
when department=2 then 'customer service'
when department=3 then 'collictions'
when department=4 then 'customer relations'
end as dept_name
from employees order by dept_name
3下面来学习使用case表达式更新列值
如: update employees
set bonus_level =
case when total_sales< 1000 then 'rookie'
when (total_sales >=1000) and (total_sales< 10000) then 'associate'
when total_sales>10000 then 'vice president'
end
除了使用实际的字符串外,可将列名和数学表达式用于计算由case表达式返回的值中
如:update employees
set commission =
case bonus_level
when 'rookie' then total_sales * 0.01
when 'associate' then total_sales * 0.05
when 'vice president' then total_sales * 0.15
end
4 使用case表达式避免错误条件
如: select id ,name case when sales_count > 0 then sales_calls/sales_count * 100.0
else 0
end as closing_percentage
from employees where department = 1
这样就避免了如果sales_count值为null或0时,产生错误。