题解 | #获取有奖金的员工相关信息。#
获取有奖金的员工相关信息。
http://www.nowcoder.com/practice/5cdbf1dcbe8d4c689020b6b2743820bf
SELECT employees.emp_no, employees.first_name, employees.last_name, emp_bonus.btype, salaries.salary,
CASE WHEN emp_bonus.btype = 1 THEN round(salaries.salary*0.1,1)
WHEN emp_bonus.btype = 2 THEN round(salaries.salary*0.2,1)
ELSE round(salaries.salary*0.3,1)
END bonus
FROM employees
left join emp_bonus on employees.emp_no=emp_bonus.emp_no
right join salaries on employees.emp_no=salaries.emp_no
WHERE salaries.to_date='9999-01-01' and emp_bonus.btype is not null
ORDER BY employees.emp_no asc
CASE WHEN emp_bonus.btype = 1 THEN round(salaries.salary*0.1,1)
WHEN emp_bonus.btype = 2 THEN round(salaries.salary*0.2,1)
ELSE round(salaries.salary*0.3,1)
END bonus
FROM employees
left join emp_bonus on employees.emp_no=emp_bonus.emp_no
right join salaries on employees.emp_no=salaries.emp_no
WHERE salaries.to_date='9999-01-01' and emp_bonus.btype is not null
ORDER BY employees.emp_no asc
