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

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