所用数据:
SELECT a.deptno, a.employename, a.salary
FROM
t_salary a
000001 李可
1000
000001 李强
2000
000001 杨彦军
4000
000002 童家道
3000
000002 姜文
3000
000002 罗文
3000
000003 窨嫡
3000
000003 童家道
3000
000003 童家道
3000
000004 于名
4000
SELECT a.deptno, a.employename, a.salary
000001
000001
000001
000002
000002
000002
000003
000003
000003
000004
使用代码:
SELECT
A.deptno, A.employename,A.salary,
-- 1 按照名称进行分区,同时按照名称进行合计
SUM (A.salary) OVER (PARTITION BY A.employename) AS SUM_INC_ONLY,
-- 2 按照名称进行累计
SUM (A.salary) OVER ( ORDER BY A.employename) AS SUM_INC,
-- 3 和 1 效果相同
SUM (A.salary) OVER (PARTITION BY A.employename ORDER BY A.employename) AS SUM_INC_NAME,
-- 4 按照部门分组,部门内进行合计。名称相同时进行累计
SUM (A.salary) OVER (PARTITION BY A.deptno ORDER BY A.employename) AS SUM_INC_DEP,
-- 5 按照部门,名称分组,部门名称相同时进行合计
SUM (A.salary) OVER (PARTITION BY A.deptno,A.employename ) AS SUM_INC_DEP_NAM
FROM t_salary A
所得结果:
DEPTNO EMPLOYENAME SALARY SUM_INC_ONLY SUM_INC SUM_INC_NAME SUM_INC_DEP SUM_INC_DEP_NAM
000002 姜文
3000
3000
3000
3000
3000
3000
000001 李可
1000
1000
4000
1000
1000
1000
000001 李强
2000
2000
6000
2000
3000
2000
000002 罗文
3000
3000
9000
3000
6000
3000
000002 童家道
3000
9000
18000
9000
9000
3000
000003 童家道
3000
9000
18000
9000
6000
6000
000003 童家道
3000
9000
18000
9000
6000
6000
000001 杨彦军
4000
4000
22000
4000
7000
4000
000004 于名
4000
4000
26000
4000
4000
4000
000003 窨嫡
3000
3000
29000
3000
9000
3000
-- 1 按照名称进行分区,同时按照名称进行合计
SUM (A.salary) OVER (PARTITION BY A.employename) AS SUM_INC_ONLY,
-- 2 按照名称进行累计
SUM (A.salary) OVER ( ORDER BY A.employename) AS SUM_INC,
-- 3
SUM (A.salary) OVER (PARTITION BY A.employename ORDER BY A.employename) AS SUM_INC_NAME,
-- 4 按照部门分组,部门内进行合计。名称相同时进行累计
SUM (A.salary) OVER (PARTITION BY A.deptno ORDER BY A.employename) AS SUM_INC_DEP,
-- 5 按照部门,名称分组,部门名称相同时进行合计
SUM (A.salary) OVER (PARTITION BY A.deptno,A.employename ) AS SUM_INC_DEP_NAM
FROM t_salary A
所得结果:
DEPTNO
000002
000001
000001
000002
000002
000003
000003
000001
000004
000003