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

select t1.emp_no,first_name,last_name,t2.btype,salary, 
(case when t2.btype=1 then t1.salary*0.1  
when t2.btype=2 then t1.salary*0.2 
else salary*0.3 end) as bonus 
FROM (select s1.emp_no,s1.first_name,s1.last_name,s2.salary 
from employees as s1 
inner join (select emp_no,salary from salaries as p1 
where exists (select * from emp_bonus as p2 where p2.emp_no=p1.emp_no) 
and to_date='9999-01-01') as s2 on s1.emp_no=s2.emp_no) as t1 
inner join emp_bonus as t2 on t1.emp_no=t2.emp_no;