CREATE TABLE emp_bonus (empno INT , received DATE , TYPE INT);
INSERT INTO emp_bonus VALUES( 7934, DATE '2005-5-17', 1 );
INSERT INTO emp_bonus VALUES( 7934, DATE '2005-2-15', 2 );
INSERT INTO emp_bonus VALUES( 7839, DATE '2005-2-15', 3 );
INSERT INTO emp_bonus VALUES( 7782, DATE '2005-2-15', 1 );
员工的奖金的根据TYPE计算,TYPE1的奖金为员工工资的10%,TYPE2的奖金为员工工资的20%,TYPE3的奖金为员工工资的30%。
现要求返回上述员工也就是部门10的所有员工的工资及奖金。
SQL> SELECT e.deptno,
2 e.empno,
3 e.ename,
4 e.sal,
5 (e.sal * CASE
6 WHEN eb.type = 1 THEN
7 0.1
8 WHEN eb.type = 2 THEN
9 0.2
10 WHEN eb.type = 3 THEN
11 0.3
12 END) AS bonus
13 FROM emp e
14 INNER JOIN emp_bonus eb ON (e.empno = eb.empno)
15 WHERE e.deptno = 10
16 ORDER BY 1, 2;
DEPTNO EMPNO ENAME SAL BONUS
---------- --
计算工资
最新推荐文章于 2022-10-28 22:22:55 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)