题解 | #获取有奖金的员工相关信息。#
获取有奖金的员工相关信息。
http://www.nowcoder.com/practice/5cdbf1dcbe8d4c689020b6b2743820bf
方法一：三个表连续连接，考察了if嵌套或者是CASE When
select b.emp_no,b.first_name,b.last_name,c.btype,t.salary,round(if(c.btype=1,t.salary0.1,if(c.btype=2,t.salary0.2,t.salary*0.3)),1) as bonus
from salaries as t join emp_bonus as c on t.emp_no=c.emp_no and t.to_date="9999-01-01" join employees as b on b.emp_no=t.emp_no order by b.emp_no;
方法二：三个表连续连接，其实方法二是最严谨的，因为把工资表作为主表可以考虑到没有btype的员工 select b.emp_no,b.first_name,b.last_name,c.btype,t.salary,round(if(c.btype=1,t.salary0.1,if(c.btype=2,t.salary0.2,t.salary*0.3)),1) as bonus from salaries as t left join emp_bonus as c on t.emp_no=c.emp_no join employees as b on b.emp_no=t.emp_no where t.to_date="9999-01-01" and c.btype is not null order by b.emp_no asc;
