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

/*
SELECT e.emp_no as emp_no, e.first_name as first_name, e.last_name as last_name, bonus.btype as btype
FROM employees as e
left join emp_bonus as bonus
on e.emp_no = bonus.emp_no

SELECT t1.emp_no as emp_no, t1.first_name as first_name, t1.last_name as last_name, t1.btype as btype, s.salary as salary
from (SELECT e.emp_no as emp_no, e.first_name as first_name, e.last_name as last_name, bonus.btype as btype
      FROM employees as e
      left join emp_bonus as bonus
      on e.emp_no = bonus.emp_no) as t1
left join salaries as s
ON s.emp_no = t1.emp_no
WHERE s.to_date = '9999-01-01'

*/
#这里不用用外连接 要用内连接
SELECT t2.emp_no, t2.first_name, t2.last_name, t2.btype, t2.salary, (case 
                                                                     when t2.btype = 1 then ROUND(t2.salary / 10, 1)
                                                                     when t2.btype = 2 then ROUND(t2.salary / 5, 1)
                                                                     else ROUND(t2.salary * 0.3, 1)
                                                                     end) as bonus
FROM (SELECT t1.emp_no as emp_no, t1.first_name as first_name, t1.last_name as last_name, t1.btype as btype, s.salary as salary
      from (SELECT e.emp_no as emp_no, e.first_name as first_name, e.last_name as last_name, bonus.btype as btype
            FROM employees as e
            join emp_bonus as bonus
            on e.emp_no = bonus.emp_no) as t1
      join salaries as s
      ON s.emp_no = t1.emp_no
      WHERE s.to_date = '9999-01-01') as t2