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

 ##哈哈哈哈哈哈哈哈哈，被自己蠢笨的方法笑到了
 #  #查找当前工资
# select
#     emp_no,
#     salary
# from
#     salaries
# where
#     to_date = '9999-01-01'
#查看各个员工号的奖金类别
# select
#     b.emp_no,
#     b.btype,
#     a.salary
# from
#     (
#         select
#             emp_no,
#             salary
#         from
#             salaries
#         where
#             to_date = '9999-01-01'
#     ) as a
#     join emp_bonus as b using (emp_no)
#计算奖金
select
    e.emp_no,
    e.first_name,
    e.last_name,
    o.btype,
    o.salary,
    round(
        (
            case
                when o.btype = 1 then o.salary * 0.1
                when o.btype = 2 then o.salary * 0.2
                else o.salary * 0.3
            end
        ),
        1
    ) as bonus
from
    employees as e
    join (
        select
            b.emp_no,
            b.btype,
            a.salary
        from
            (
                select
                    emp_no,
                    salary
                from
                    salaries
                where
                    to_date = '9999-01-01'
            ) as a
            join emp_bonus as b using (emp_no)
    ) as o using (emp_no)
order by
    emp_no