oracle中over()方法的用法

Oracle SQL中OVER()函数的使用示例解析

最新推荐文章于 2025-10-13 15:57:45 发布

原创最新推荐文章于 2025-10-13 15:57:45 发布 · 2w 阅读

17 ·

CC 4.0 BY-SA版权

本文通过示例详细介绍了Oracle SQL中OVER()函数的用法，包括按员工姓名连续求和、按部门连续求和、按部门总和以及综合案例，展示了OVER()在不同场景下的功能和效果。

SQL> select deptno,ename,sal
2 from emp
3 order by deptno;

DEPTNO ENAME SAL

    10 CLARK          2450
       KING          5000
       MILLER           1300

    20 SMITH          800
       ADAMS          1100
       FORD          3000
       SCOTT          3000
       JONES          2975

    30 ALLEN          1600
       BLAKE          2850
       MARTIN           1250
       JAMES          950
       TURNER           1500
       WARD          1250

已选择14行。

2.先来一个简单的，注意over(…)条件的不同，
使用 sum(sal) over (order by ename)… 查询员工的薪水“连续”求和,
注意over (order by ename)如果没有order by 子句，求和就不是“连续”的，
放在一起，体会一下不同之处：

SQL> select deptno,ename,sal,
2 sum(sal) over (order by ename) 连续求和,
3 sum(sal) over () 总和, – 此处sum(sal) over () 等同于sum(sal)
4 100*round(sal/sum(sal) over (),4) “份额(%)”
5 from emp
6 /

DEPTNO ENAME SAL 连续求和总和份额(%)

   20 ADAMS          1100    1100    29025    3.79
    30 ALLEN          1600    2700    29025    5.51
    30 BLAKE          2850    5550    29025    9.82
    10 CLARK          2450    8000    29025    8.44
    20 FORD          3000    11000    29025    10.34
    30 JAMES          950    11950    29025    3.27
    20 JONES          2975    14925    29025    10.25
    10 KING          5000    19925    29025    17.23
    30 MARTIN           1250    21175    29025    4.31
    10 MILLER           1300    22475    29025    4.48
    20 SCOTT          3000    25475    29025    10.34
    20 SMITH          800    26275    29025    2.76
    30 TURNER           1500    27775    29025    5.17
    30 WARD          1250    29025    29025    4.31

已选择14行。

3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(…)条件的不同，
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。

SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by ename) 部门连续求和,–各部门的薪水"连续"求和
3 sum(sal) over (partition by deptno) 部门总和, – 部门统计的总和，同一部门总和不变
4 100round(sal/sum(sal) over (partition by deptno),4) “部门份额(%)”,
5 sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
6 sum(sal) over () 总和, – 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
7 100round(sal/sum(sal) over (),4) “总份额(%)”
8 from emp
9 /

DEPTNO ENAME SAL 部门连续求和部门总和部门份额(%) 连续求和总和总份额(%)

10 CLARK 2450 2450 8750 28 2450 29025 8.44
KING 5000 7450 8750 57.14 7450 29025 17.23
MILLER 1300 8750 8750 14.86 8750 29025 4.48

20 ADAMS 1100 1100 10875 10.11 9850 29025 3.79
FORD 3000 4100 10875 27.59 12850 29025 10.34
JONES 2975 7075 10875 27.36 15825 29025 10.25
SCOTT 3000 10075 10875 27.59 18825 29025 10.34
SMITH 800 10875 10875 7.36 19625 29025 2.76

30 ALLEN 1600 1600 9400 17.02 21225 29025 5.51
BLAKE 2850 4450 9400 30.32 24075 29025 9.82
JAMES 950 5400 9400 10.11 25025 29025 3.27
MARTIN 1250 6650 9400 13.3 26275 29025 4.31
TURNER 1500 8150 9400 15.96 27775 29025 5.17
WARD 1250 9400 9400 13.3 29025 29025 4.31

已选择14行。

4.来一个综合的例子，求和规则有按部门分区的，有不分区的例子
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp;

DEPTNO ENAME SAL DEPT_SUM SUM

    10 MILLER           1300    1300    1300
       CLARK          2450    3750    3750
       KING          5000    8750    8750

    20 SMITH          800        800    9550
       ADAMS          1100    1900    10650
       JONES          2975    4875    13625
       SCOTT          3000    10875    19625
       FORD          3000    10875    19625

    30 JAMES          950        950    20575
       WARD          1250    3450    23075
       MARTIN           1250    3450    23075
       TURNER           1500    4950    24575
       ALLEN          1600    6550    26175
       BLAKE          2850    9400    29025

已选择14行。

5.来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。

SQL> select deptno,ename,sal,
2 sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,
3 sum(sal) over (order by deptno desc,sal desc) sum
4 from emp;

DEPTNO ENAME SAL DEPT_SUM SUM

    30 BLAKE          2850    2850    2850
       ALLEN          1600    4450    4450
       TURNER           1500    5950    5950
       WARD          1250    8450    8450
       MARTIN           1250    8450    8450
       JAMES          950    9400    9400

    20 SCOTT          3000    6000    15400
       FORD          3000    6000    15400
       JONES          2975    8975    18375
       ADAMS          1100    10075    19475
       SMITH          800    10875    20275

    10 KING          5000    5000    25275
       CLARK          2450    7450    27725
       MILLER           1300    8750    29025

已选择14行。

6.体会：在"… from emp;"后面不要加order by 子句，使用的分析函数的(partition by deptno order by sal)
里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了。如：

SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
2 sum(sal) over (order by deptno,sal) sum
3 from emp
4 order by deptno desc;

DEPTNO ENAME SAL DEPT_SUM SUM

    30 JAMES          950        950    20575
       WARD          1250    3450    23075
       MARTIN           1250    3450    23075
       TURNER           1500    4950    24575
       ALLEN          1600    6550    26175
       BLAKE          2850    9400    29025

    20 SMITH          800        800    9550
       ADAMS          1100    1900    10650
       JONES          2975    4875    13625
       SCOTT          3000    10875    19625
       FORD          3000    10875    19625

    10 MILLER           1300    1300    1300
       CLARK          2450    3750    3750
       KING          5000    8750    8750

已选择14行