题解 | #获取有奖金的员工相关信息。#
获取有奖金的员工相关信息。
http://www.nowcoder.com/practice/5cdbf1dcbe8d4c689020b6b2743820bf
用case when分类求bonus
select e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
case when b.btype = 1 then s.salary*0.1
when b.btype = 2 then s.salary*0.2
else s.salary*0.3
end bonus
from employees e
join emp_bonus b
on e.emp_no=b.emp_no
join salaries s
on s.emp_no=e.emp_no
where s.to_date='9999-01-01'
order by e.emp_no;
case when b.btype = 1 then s.salary*0.1
when b.btype = 2 then s.salary*0.2
else s.salary*0.3
end bonus
from employees e
join emp_bonus b
on e.emp_no=b.emp_no
join salaries s
on s.emp_no=e.emp_no
where s.to_date='9999-01-01'
order by e.emp_no;