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

# 先连接employees和emp_bonus表
select e.emp_no, first_name, last_name, btype
from employees e
join emp_bonus eb
on e.emp_no=eb.emp_no

# 再将上表与salaries表连接
select t1.*, salary,
case btype
when 1 then round(salary*0.1, 1)
when 2 then round(salary*0.2, 1)
else round(salary*0.3, 1)
end as bonus
from (
    select e.emp_no, first_name, last_name, btype
    from employees e
    join emp_bonus eb
    on e.emp_no=eb.emp_no
) t1
join salaries s
on t1.emp_no=s.emp_no and to_date = '9999-01-01'
order by emp_no;