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

考察表连接和 CASE WHEN
连接后的大表中，用 case when 去判断 bonus

select c.emp_no
      ,c.first_name
      ,c.last_name
      ,d.btype
      ,c.salary
      ,(case when d.btype=1 then c.salary*0.1
             when d.btype=2 then c.salary*0.2
        else c.salary*0.3 
        end) as bonus
from
     (select a.emp_no
            ,a.first_name
            ,a.last_name
            ,b.salary
      from employees a
      left join salaries b
      on a.emp_no=b.emp_no
      where b.to_date='9999-01-01') c
left  join emp_bonus d
on c.emp_no= d.emp_no
where d.btype is not null