oracle over()函数技术详解2

来一个综合的例子，求和规则有按部门分区的，有不分区的例子

 

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行