题解 | #获取有奖金的员工相关信息。#
获取有奖金的员工相关信息。
http://www.nowcoder.com/practice/5cdbf1dcbe8d4c689020b6b2743820bf
【思路】
1.要想得到表中的btype和salary是比较简单的，用表连接就可以得到
2.最后用 case when生成bonus
【代码如下】
select t1., t2.salary, case
when t1.btype=1 then salary0.1
when t1.btype=2 then salary0.2
else salary0.3
end as bonus
from
(select e.emp_no, e.first_name, e.last_name, eb.btype from employees as e inner join emp_bonus as eb on e.emp_no = eb.emp_no) as t1
left join
(select s.emp_no, s.salary from salaries as s where s.to_date = '9999-01-01') as t2
on t1.emp_no = t2.emp_no
最初在生成t1表时用了left join出错，因为可能有员工没有bonus,因此要用inner join。
查看18道真题和解析
基恩士成长空间 453人发布