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

select
e1.emp_no
,e1.first_name
,e1.last_name
,eb1.btype
,s1.salary
,s1.salary0.1 bonus
from employees e1
join emp_bonus eb1
on e1.emp_no = eb1.emp_no
join salaries s1
on s1.emp_no = e1.emp_no
where eb1.btype = 1
and s1.to_date = '9999-01-01'
union
select
e1.emp_no
,e1.first_name
,e1.last_name
,eb1.btype
,s1.salary
,s1.salary0.2 bonus
from employees e1
join emp_bonus eb1
on e1.emp_no = eb1.emp_no
join salaries s1
on s1.emp_no = e1.emp_no
where eb1.btype = 2
and s1.to_date = '9999-01-01'
union
select
e1.emp_no
,e1.first_name
,e1.last_name
,eb1.btype
,s1.salary
,s1.salary*0.3 bonus
from employees e1
join emp_bonus eb1
on e1.emp_no = eb1.emp_no
join salaries s1
on s1.emp_no = e1.emp_no
where eb1.btype <> 1
and eb1.btype <> 2
and s1.to_date = '9999-01-01'
order by emp_no
有点长 本人不知道case函数 使用union连接，思路很简单
但是最后order by加不加没啥 加order by是为了让机器检查通过的