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

select s.emp_no,first_name,last_name,eb.btype,s.salary, 
case when eb.btype = 1 then s.salary*0.1
     when eb.btype = 2 then s.salary*0.2
     else s.salary*0.3
end bonus
from salaries s
left join employees e on s.emp_no = e.emp_no
left join emp_bonus eb on s.emp_no = eb.emp_no 
where to_date = '9999-01-01' and btype is not null
order by s.emp_no asc

这个题主要考察的是 case when ... then ...语法，要注意的一点是这一段要写在select阶段，别名写在end后即可。题目没有表达清楚，其实是只要输出有奖金的员工。