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

不使用case，就是将所有的情况列出来

select employees.emp_no,first_name, last_name, cal_emp_bonus.btype,salary,salary*cal_emp_bonus.rate  as bonus from
    salaries,
    employees,
    (
        select emp_no, btype, btype*0.1 as rate from emp_bonus where btype in(1,2)
        union
        select emp_no, btype, 0.3 as rate from emp_bonus where btype not in(1,2)
    ) as cal_emp_bonus
where employees.emp_no = salaries.emp_no
and employees.emp_no = cal_emp_bonus.emp_no
and to_date = "9999-01-01"
order by emp_no