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

with t0 as(
    #筛选出有奖金的员工
    select e.emp_no, first_name,last_name,btype
    from  employees e  join emp_bonus eb  on  e.emp_no = eb.emp_no
)
,t1 as(
    # 筛选出所有员工当前的工资
    select
    emp_no,
    salary
    from salaries 
    where to_date='9999-01-01'
)
select 
t0.emp_no,
first_name,
last_name,
btype,
salary,
case 
    when btype =1  then salary*0.1
    when btype =2  then salary*0.2
    else salary*0.3
end as bonus
from t0 left join t1 on t0.emp_no = t1.emp_no