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

select
    e.emp_no,
    first_name,
    last_name,
    btype,
    salary,
    round(bonus1, 1) bonus
from
    (
        select
            b.emp_no,
            btype,
            salary,
            case
                when btype = 1 then salary * 0.1
                when btype = 2 then salary * 0.2
                else salary * 0.3
            end bonus1
        from
            emp_bonus b
            join salaries s on b.emp_no = s.emp_no
        where
            to_date = '9999-01-01'
    ) rk
    join employees e on rk.emp_no = e.emp_no
order by
    emp_no