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

方法一 简单方法

SELECT e.emp_no
        ,e.first_name
        ,e.last_name
        ,eb.btype
        ,s.salary
        ,s.salary*(eb.btype*0.1) AS bonus
FROM employees e
JOIN salaries s on e.emp_no = s.emp_no
JOIN emp_bonus eb on e.emp_no = eb.emp_no
WHERE s.to_date='9999-01-01'

方法二 CASE WHEN 方法

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 bonus
FROM employees e
JOIN salaries s on e.emp_no = s.emp_no
JOIN emp_bonus eb on e.emp_no = eb.emp_no
WHERE s.to_date='9999-01-01'