题解 | #获取有奖金的员工相关信息。#
获取有奖金的员工相关信息。
https://www.nowcoder.com/practice/5cdbf1dcbe8d4c689020b6b2743820bf
SELECT emp_no,first_name,last_name,btype,salary,
ROUND(
CASE
WHEN btype = 1 THEN salary * 0.1
WHEN btype = 2 THEN salary * 0.2
ELSE salary * 0.3
END
,1) bonus
FROM employees
JOIN emp_bonus USING(emp_no)
JOIN salaries USING(emp_no)
WHERE to_date = '9999-01-01'
1、津贴计算
CASE
WHEN btype = 1 THEN salary * 0.1
WHEN btype = 2 THEN salary * 0.2
ELSE salary * 0.3
END
2、当前薪水判定
WHERE to_date = '9999-01-01'
3、将相关信息输出
SELECT emp_no,first_name,last_name,btype,salary,
ROUND(
CASE
WHEN btype = 1 THEN salary * 0.1
WHEN btype = 2 THEN salary * 0.2
ELSE salary * 0.3
END
,1) bonus
FROM employees
JOIN emp_bonus USING(emp_no)
JOIN salaries USING(emp_no)
WHERE to_date = '9999-01-01'
