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

非常好理解，解法暴力，就是忘了还能循环，写这么老长

select x.emp_no,e.first_name, x.btype, x.salary, x.bonus
from
(select s.emp_no, s.salary, s.salary*0.1 bonus, b.btype
from salaries s join emp_bonus b on s.emp_no= b.emp_no
where b.btype = 1 and s.to_date='9999-01-01'
UNION
select s.emp_no, s.salary, s.salary*0.2 bonus, b.btype
from salaries s join emp_bonus b on s.emp_no= b.emp_no
where b.btype = 2 and s.to_date='9999-01-01'
UNION
select s.emp_no, s.salary, s.salary*0.3 bonus, b.btype
from salaries s join emp_bonus b on s.emp_no= b.emp_no
where b.btype not in ('1','2') and s.to_date='9999-01-01') as x
join employees e on x.emp_no = e.emp_no