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

知识点：

1.Mysql取一位小数 round(100.123, 1) -> 100.1

2.case when的语法

3.注意审题，只要有奖金的员工

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