题解 | #获取有奖金的员工相关信息。#
获取有奖金的员工相关信息。
http://www.nowcoder.com/practice/5cdbf1dcbe8d4c689020b6b2743820bf
两种方法IF和CASE WHEN
SELECT em.emp_no,em.first_name,em.last_name,eb.btype,sa.salary,
(case eb.btype when 1 then sa.salary*0.1
when 2 then sa.salary*0.2
else sa.salary*0.3
end) bonus
#IF(eb.btype=1,0.1*sa.salary,IF(eb.btype=2,0.2*sa.salary,0.3*sa.salary)) bonus
FROM employees em JOIN emp_bonus eb ON em.emp_no=eb.emp_no
LEFT JOIN salaries sa ON em.emp_no=sa.emp_no
WHERE sa.to_date ='9999-01-01'