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

with t1 as (
    select a.emp_no, a.recevied, b.first_name, b.last_name, a.btype
    from emp_bonus as a
    left join employees as b
    on a.emp_no = b.emp_no
)
select t1.emp_no, t1.first_name, t1.last_name, t1.btype, c.salary, 
(case when t1.btype = 1 then round(c.salary*0.1, 1) when t1.btype = 2 then round(c.salary*0.2, 1) else round(salary*0.3, 1) end ) as bonus
from salaries as c
right join t1
on t1.emp_no = c.emp_no and c.to_date = '9999-01-01'
order by emp_no;