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

with employeed_salary as (
    select
        emp_no,
        salary
    from 
        salaries
    where 
        to_date = '9999-01-01'
),

employeed_salary_bonus as (
    select
        e_s.emp_no,
        e_s.salary,
        e_b.btype,
        case e_b.btype
            when 1 then round(e_s.salary * 0.1,1)
            when 2 then round(e_s.salary * 0.2,1)
            else round(e_s.salary * 0.3,1)
        end as bonus
    from 
        employeed_salary as e_s
    left join 
        emp_bonus as e_b
    on 
        e_s.emp_no = e_b.emp_no
    having 
        e_b.btype is not null
)

select
    e_s_b.emp_no,
    e.first_name,
    e.last_name,
    e_s_b.btype,
    e_s_b.salary,
    e_s_b.bonus
from 
    employeed_salary_bonus as e_s_b
left join 
    employees as e
on 
    e_s_b.emp_no = e.emp_no