oracle 分組累加

今天看到論壇中有關於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分析函數的方式更好一些．當然這也不是絕對的．