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

select 
b.emp_no,
e.first_name, 
e.last_name, 
b.btype,
s.salary,
case when b.btype=1 then s.salary*0.1
    when b.btype=2 then s.salary*0.2
    else s.salary*0.3 end as bonus
from employees e
join (select emp_no, salary, from_date, to_date from salaries s
where to_date='9999-01-01') s
join emp_bonus b
on e.emp_no=s.emp_no
and  e.emp_no=b.emp_no
and b.recevied between s.from_date and s.to_date
# group by emp_no
order by emp_no