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

with tmp as (
select t1.*,t2.btype,t3.first_name,t3.last_name
from salaries as t1
inner join emp_bonus as t2 on t1.emp_no = t2.emp_no
inner join employees as t3 on t2.emp_no = t3.emp_no
where t1.to_date = '9999-01-01')

select 
emp_no
,first_name
,last_name
,btype
,salary
,case when btype = 1 then round(salary * 0.1,1) when btype = 2 then round(salary * 0.2,1) else 0.3*salary end as bonus
from tmp
order by emp_no asc;