知识点

  1. case [col_name] when [value1] then [result1]…else [default] end： 枚举这个字段所有可能的值
  2. 内连接三个表。筛选条件是现在的薪水to_date = '9999-01-01'

代码

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