分组函数解析

分组函数:

select sal,deptno,sum(sal) over (partition by deptno) from emp; ---按部门求总和
SQL> select sal,deptno,sum(sal) over (partition by deptno) from emp;

      SAL DEPTNO SUM(SAL)OVER(PARTITIONBYDEPTNO
--------- ------ ------------------------------
  2450.00     10                           8750
  5000.00     10                           8750
  1300.00     10                           8750
  2975.00     20                          10875
  3000.00     20                          10875
  1100.00     20                          10875
   800.00     20                          10875
  3000.00     20                          10875
  1250.00     30                           9400
  1500.00     30                           9400
  1600.00     30                           9400
   950.00     30                           9400
  2850.00     30                           9400
  1250.00     30                           9400

14 rows selected

select sal,deptno,sum(sal) over () from emp; -------不按部门，求所有员工总和，效果等同于sum(sal)。

SQL> select sal,deptno,sum(sal) over () from emp;

      SAL DEPTNO SUM(SAL)OVER()
--------- ------ --------------
   800.00     20          29025
  1600.00     30          29025
  1250.00     30          29025
  2975.00     20          29025
  1250.00     30          29025
  2850.00     30          29025
  2450.00     10          29025
  3000.00     20          29025
  5000.00     10          29025
  1500.00     30          29025
  1100.00     20          29025
   950.00     30          29025
  3000.00     20          29025
  1300.00     10          29025

14 rows selected


select sal,deptno,max(sal) over (partition by deptno) from emp; ---按部门求最大值
SQL> select sal,deptno,max(sal) over (partition by deptno) from emp;

      SAL DEPTNO MAX(SAL)OVER(PARTITIONBYDEPTNO
--------- ------ ------------------------------
  2450.00     10                           5000
  5000.00     10                           5000
  1300.00     10                           5000
  2975.00     20                           3000
  3000.00     20                           3000
  1100.00     20                           3000
   800.00     20                           3000
  3000.00     20                           3000
  1250.00     30                           2850
  1500.00     30                           2850
  1600.00     30                           2850
   950.00     30                           2850
  2850.00     30                           2850
  1250.00     30                           2850

14 rows selected





select sal,deptno,max(sal) over () from emp; ---按所有员工中的最大值

SQL> select sal,deptno,max(sal) over () from emp;

      SAL DEPTNO MAX(SAL)OVER()
--------- ------ --------------
   800.00     20           5000
  1600.00     30           5000
  1250.00     30           5000
  2975.00     20           5000
  1250.00     30           5000
  2850.00     30           5000
  2450.00     10           5000
  3000.00     20           5000
  5000.00     10           5000
  1500.00     30           5000
  1100.00     20           5000
   950.00     30           5000
  3000.00     20           5000
  1300.00     10           5000

14 rows selected