题解 | #获取有奖金的员工相关信息。#
获取有奖金的员工相关信息。
http://www.nowcoder.com/practice/5cdbf1dcbe8d4c689020b6b2743820bf
SELECT DISTINCT emp_no,first_name,last_name,btype,salary,
(CASE WHEN btype = 1 THEN salary0.1
WHEN btype = 2 THEN salary0.2
ELSE salary*0.3 END) bonus
FROM(SELECT e.emp_no,first_name,last_name,btype,salary, s.from_date
FROM employees e JOIN salaries s ON e.emp_no = s.emp_no
JOIN emp_bonus eb ON e.emp_no = eb.emp_no
JOIN (SELECT MAX(from_date) maxd FROM salaries GROUP BY emp_no)A ON s.from_date = maxd)B;