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

解题思路 1、先找到当前员工的工资和信息

2、查询到当前员工的奖金率

SELECT t1.emp_no,t1.first_name,t1.last_name, btype,t1.salary,

case btype

WHEN 1 THEN salary*0.1

WHEN 2 THEN salary*0.2

ELSE salary* 0.3 END bonus

FROM  (SELECT e.emp_no,e.first_name,e.last_name,s.salary FROM

      employees e JOIN salaries s ON

      e.emp_no = s.emp_no WHERE s.to_date = '9999-01-01') t1

      JOIN emp_bonus emp

      ON t1.emp_no = emp.emp_no ORDER BY emp_no;