题解 | #获取有奖金的员工相关信息。#

select 
em.emp_no
,em.first_name
,em.last_name
,eb.btype
,sa.salary
,case 
when eb.btype=1 then sa.salary*0.1 
when eb.btype=2 then sa.salary*0.2
else sa.salary*0.3
end as bonus
from employees em join emp_bonus eb 
on em.emp_no = eb.emp_no
join salaries sa
on em.emp_no = sa.emp_no
where  to_date='9999-01-01'
order by em.emp_no

本题需用到 case when 函数

用法复习：

case when 条件1 then 结果1

when 条件2 then 结果2

when 条件n then 结果n

else 其余条件返回的值

end

先用 to_date='9999-01-01'筛选出当前工资

根据对应btype类型计算对应奖金即可