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.

 

 

评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值