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

select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,
round((case eb.btype 
when 1 then round(0.1*s.salary,1)
when 2 then 0.2*s.salary
else 0.3*s.salary end) , 1) bonus
from employees e
inner join
emp_bonus eb
on e.emp_no=eb.emp_no
inner join
salaries s
on s.emp_no=e.emp_no
and s.to_date='9999-01-01'
order by e.emp_no

• 考察case when的语法

SELECT 
  column1,
  CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
  END AS new_column
FROM table;

• 以及round四舍五入控制数字位数
• 同时考察表的连接
• 只有在bonus表中的员工才会有奖金
• 连接要以bonus表为准