今天看到論壇中有關於Oracle實現累加功能的問題,為瞭解決疑問,下面為在Oracle中實現累加的2種方法.
============================Question================================
SQL> select * from emp;
EMPID EMPNAME SALARY DEPTID
---------- -------------------- ---------- ----------
1 Xie_Feng 8000 1
2 mantisXF 3000 1
3 Roger 6000 1
4 Tony 2500 1
5 Ning 3800 1
6 Sandip 7000 2
7 Akanksha 5500 2
8 Subhash 4800 2
8 rows selected
SQL> select * from dept;
DEPTID DEPTNAME
---------- --------------------
1 DWH
2 BODI
SQL>
請問如何實現salary工資的累加?謝謝.
=========================Resolution_1=====================================
Analysis: 用SUM分析函數實現
如果需要按照Dept部門分組的話:
SQL> SELECT D.DEPTID,
2 D.DEPTNAME,
3 SUM(E.SALARY) OVER(PARTITION BY D.DEPTID ORDER BY D.DEPTID, E.EMPID) AS ACCUMULATE_SALARY_GROUP_DEPT
4 FROM EMP E, DEPT D
5 WHERE E.DEPTID = D.DEPTID;
DEPTID DEPTNAME ACCUMULATE_SALARY_GROUP_DEPT
---------- -------------------- ----------------------------
1 DWH 8000
1 DWH 11000
1 DWH 17000
1 DWH 19500
1 DWH 23300
2 BODI 7000
2 BODI 12500
2 BODI 17300
8 rows selected
如果不需要按照Dept部門分組的話, 也就是直接累加所有的Salary工資:
SQL> SELECT E.EMPID,
2 E.EMPNAME,
3 E.DEPTID,
4 SUM(E.SALARY) OVER(ORDER BY E.EMPID) AS ACCUMULATE_SALARY_GROUP_DEPT
5 FROM EMP E;
EMPID EMPNAME DEPTID ACCUMULATE_SALARY_GROUP_DEPT
---------- -------------------- ---------- ----------------------------
1 Xie_Feng 1 8000
2 mantisXF 1 11000
3 Roger 1 17000
4 Tony 1 19500
5 Ning 1 23300
6 Sandip 2 30300
7 Akanksha 2 35800
8 Subhash 2 40600
8 rows selected
=========================Resolution_2=====================================
Analysis: 用SUM+GROUP BY實現
一樣,如果需要按照Dept部門分組的話:
SQL> SELECT E1.EMPID, E1.EMPNAME, E2.DEPTID, SUM(E2.SALARY)
2 FROM EMP E1, EMP E2
3 WHERE E1.DEPTID = E2.DEPTID
4 AND E1.EMPID >= E2.EMPID
5 GROUP BY E1.EMPID, E1.EMPNAME, E2.DEPTID;
EMPID EMPNAME DEPTID SUM(E2.SALARY)
---------- -------------------- ---------- --------------
1 Xie_Feng 1 8000
2 mantisXF 1 11000
3 Roger 1 17000
4 Tony 1 19500
5 Ning 1 23300
6 Sandip 2 7000
7 Akanksha 2 12500
8 Subhash 2 17300
8 rows selected
一樣同上,如果不需要按照Dept部門分組的話, 也就是直接累加所有的Salary工資:
SQL> SELECT E1.EMPID, E1.EMPNAME, SUM(E2.SALARY)
2 FROM EMP E1, EMP E2
3 WHERE E1.EMPID >= E2.EMPID
4 GROUP BY E1.EMPID, E1.EMPNAME;
EMPID EMPNAME SUM(E2.SALARY)
---------- -------------------- --------------
1 Xie_Feng 8000
2 mantisXF 11000
3 Roger 17000
4 Tony 19500
5 Ning 23300
6 Sandip 30300
7 Akanksha 35800
8 Subhash 40600
8 rows selected
Summary: 總共兩種實現方法,單從效率來說如果你的表數據很多的話,用SUM分析函數的方式更好一些.當然這也不是絕對的.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment