题解 | #获取有奖金的员工相关信息。#
获取有奖金的员工相关信息。
https://www.nowcoder.com/practice/5cdbf1dcbe8d4c689020b6b2743820bf
with t1 as ( select a.emp_no, a.recevied, b.first_name, b.last_name, a.btype from emp_bonus as a left join employees as b on a.emp_no = b.emp_no ) select t1.emp_no, t1.first_name, t1.last_name, t1.btype, c.salary, (case when t1.btype = 1 then round(c.salary*0.1, 1) when t1.btype = 2 then round(c.salary*0.2, 1) else round(salary*0.3, 1) end ) as bonus from salaries as c right join t1 on t1.emp_no = c.emp_no and c.to_date = '9999-01-01' order by emp_no;