题解 | #获取有奖金的员工相关信息。#
获取有奖金的员工相关信息。
http://www.nowcoder.com/practice/5cdbf1dcbe8d4c689020b6b2743820bf
知识点
- case [col_name] when [value1] then [result1]…else [default] end： 枚举这个字段所有可能的值
- 内连接三个表。筛选条件是现在的薪水to_date = '9999-01-01'
代码
select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
(case eb.btype
when 1 then s.salary * 0.1
when 2 then s.salary * 0.2
else s.salary * 0.3
end
) as bonus
from employees as e
join emp_bonus as eb
on e.emp_no = eb.emp_no
join salaries as s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
