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分析函数的方式更好一些．当然这也不是绝对的．

 

Hope it will helpful for your issues! Thanks.