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
---------- ---------- ---------- ---------- ----------
10 7782 CLARK 2450 245
10 7839 KING 5000 1500
10 7934 MILLER 1300 130
10 7934 MILLER 1300 260
SQL> SELECT eb.empno,
2 (CASE
3 WHEN eb.type = 1 THEN
4 0.1
5 WHEN eb.type = 2 THEN
6 0.2
7 WHEN eb.type = 3 THEN
8 0.3
9 END) AS rate
10 FROM emp_bonus eb
11 ORDER BY 1, 2;
EMPNO RATE
---------- ----------
7782 .1
7839 .3
7934 .1
7934 .2
SQL> SELECT e.deptno,
2 SUM(e.sal) AS total_sal,
3 SUM(e.sal * CASE
4 WHEN eb.type = 1 THEN
5 0.1
6 WHEN eb.type = 2 THEN
7 0.2
8 WHEN eb.type = 3 THEN
9 0.3
10 END) AS total_bonus
11 FROM emp e
12 INNER JOIN emp_bonus eb ON (e.empno = eb.empno)
13 WHERE e.deptno = 10
14 GROUP BY e.deptno;
DEPTNO TOTAL_SAL TOTAL_BONUS
---------- ---------- -----------
10 10050 2135
此时MILLER 工资被计算了2次
正确写法:
SQL> SELECT e.deptno,
2 SUM(e.sal) AS total_sal,
3 SUM(e.sal * eb2.rate) AS total_bonus
4 FROM emp e
5 INNER JOIN (SELECT eb.empno,
6 SUM(CASE
7 WHEN eb.type = 1 THEN
8 0.1
9 WHEN eb.type = 2 THEN
10 0.2
11 WHEN eb.type = 3 THEN
12 0.3
13 END) AS rate
14 FROM emp_bonus eb
15 GROUP BY eb.empno) eb2 ON eb2.empno = e.empno
16 WHERE e.deptno = 10
17 GROUP BY e.deptno;
DEPTNO TOTAL_SAL TOTAL_BONUS
---------- ---------- -----------
10 8750 2135