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

1.连接employees，emp_bonus，salaries三张表，限制salaries的to_date字段='9999-01-01'。最后选出所需字段并用case将btype分出三种情况并分别算出bonus。

select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
(case
when eb.btype = 1 then s.salary * 0.1
when eb.btype = 2 then s.salary * 0.2
else s.salary * 0.3
end) as bonus
from employees as e, emp_bonus as eb, salaries as s
where e.emp_no = eb.emp_no and 
      e.emp_no = s.emp_no and s.to_date = '9999-01-01'     

2.和第一种办法逻辑相同，但将case换成了两层if嵌套。

select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
if(eb.btype = 1, s.salary * 0.1, if(eb.btype = 2, s.salary * 0.2, s.salary * 0.3)) as bonus
from employees as e, emp_bonus as eb, salaries as s
where e.emp_no = eb.emp_no and 
      e.emp_no = s.emp_no and s.to_date = '9999-01-01'