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

select t1.emp_no
,first_name
,last_name
,btype
,salary
,round((case
            when btype=1 or btype=2 then salary*(btype/10)
        else salary*0.3
        end),1) bonus
from(select b.emp_no
    ,first_name
    ,last_name
    ,btype
    from emp_bonus b left join employees using(emp_no)) t1
    join
    (select emp_no
    ,sum(salary) salary
    from salaries
    where to_date='9999-01-01'
    group by emp_no ) t2
    on t1.emp_no = t2.emp_no