Oracle分组查询 over (parttion by xxx order by xxx)

来源：互联网发布：淘宝补差价怎么弄编辑：程序博客网时间：2024/05/22 00:14

over不能单独使用，要和函数：rank(),dense_rank(),row_number(),sum(),min(),max()等一起使用，下面以实例说明

采用的数据来源于scott用户。

SQL> select * from emp;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH      CLERK      7902 1980/12/17     800.00               20 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20 7839 KING       PRESIDENT       1981/11/17    5000.00               10 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20 7900 JAMES      CLERK      7698 1981/12/3      950.00               30 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20 7934 MILLER     CLERK      7782 1982/1/23     1300.00               1014 rows selected

一。.各种求和

select deptno,       ename,       sal,       sum(sal) over() 公司总工资,       100 * round(sal / （sum(sal) over() ）, 4) 工资百分比,       sum(sal) over(order by ename) 公司内工资递加,       sum(sal) over(partition by deptno order by ename) 部门内工资递加  from emp order by deptno;DEPTNO ENAME       SAL     公司总工资  工资百分比  公司内工资递加 部门内工资递加------ ---------- --------- ---------- ---------- -------------- --------------    10 CLARK        2450.00      29025       8.44           8000           2450    10 KING         5000.00      29025      17.23          19925           7450    10 MILLER       1300.00      29025       4.48          22475           8750    20 ADAMS        1100.00      29025       3.79           1100           1100    20 FORD         3000.00      29025      10.34          11000           4100    20 JONES        2975.00      29025      10.25          14925           7075    20 SCOTT        3000.00      29025      10.34          25475          10075    20 SMITH         800.00      29025       2.76          26275          10875    30 ALLEN        1600.00      29025       5.51           2700           1600    30 BLAKE        2850.00      29025       9.82           5550           4450    30 JAMES         950.00      29025       3.27          11950           5400    30 MARTIN       1250.00      29025       4.31          21175           6650    30 TURNER       1500.00      29025       5.17          27775           8150    30 WARD         1250.00      29025       4.31          29025           940014 rows selected

二。分组排序

rank(),dense_rank(),row_number() 都是排序，但有区别

rank() 是允许并行并跳跃拍序，会跳过空序号
dense_rank() 是允许并行并连续排序
row_number() 是不允许并行

1.部门内按工资排名,允许并行并跳过空序号

select deptno,       ename,       sal,       rank() over(partition by deptno order by sal desc) rank  from emp;DEPTNO ENAME            SAL       RANK------ ---------- --------- ----------    10 KING         5000.00          1    10 CLARK        2450.00          2    10 MILLER       1300.00          3    20 SCOTT        3000.00          1    20 FORD         3000.00          1    20 JONES        2975.00          3    20 ADAMS        1100.00          4    20 SMITH         800.00          5    30 BLAKE        2850.00          1    30 ALLEN        1600.00          2    30 TURNER       1500.00          3    30 MARTIN       1250.00          4    30 WARD         1250.00          4    30 JAMES         950.00          614 rows selected

2.部门内按工资排名,允许并列不跳过空序号

select deptno,       ename,       sal,       dense_rank() over(partition by deptno order by sal desc) rank  from emp;DEPTNO ENAME            SAL       RANK------ ---------- --------- ----------    10 KING         5000.00          1    10 CLARK        2450.00          2    10 MILLER       1300.00          3    20 SCOTT        3000.00          1    20 FORD         3000.00          1    20 JONES        2975.00          2    20 ADAMS        1100.00          3    20 SMITH         800.00          4    30 BLAKE        2850.00          1    30 ALLEN        1600.00          2    30 TURNER       1500.00          3    30 MARTIN       1250.00          4    30 WARD         1250.00          4    30 JAMES         950.00          514 rows selected

3.部门内按工资排名,不允许并列

select deptno,       ename,       sal,       row_number() over(partition by deptno order by sal desc) rank  from emp;  DEPTNO ENAME            SAL       RANK------ ---------- --------- ----------    10 KING         5000.00          1    10 CLARK        2450.00          2    10 MILLER       1300.00          3    20 SCOTT        3000.00          1    20 FORD         3000.00          2    20 JONES        2975.00          3    20 ADAMS        1100.00          4    20 SMITH         800.00          5    30 BLAKE        2850.00          1    30 ALLEN        1600.00          2    30 TURNER       1500.00          3    30 MARTIN       1250.00          4    30 WARD         1250.00          5    30 JAMES         950.00          614 rows selected

三。求分组最高最低 max() min()

select deptno,       ename,       sal,       max(sal) over() 公司最高,       min(sal) over(partition by deptno) 部门最低,       max(sal) over(partition by deptno) 部门最高,       nvl(sal - min(sal) over(partition by deptno), 0) 比最低多  from emp;DEPTNO ENAME       SAL       公司最高  部门最低   部门最高       比最低多------ ---------- --------- ---------- ---------- ---------- ----------    10 CLARK        2450.00       5000       1300       5000       1150    10 KING         5000.00       5000       1300       5000       3700    10 MILLER       1300.00       5000       1300       5000          0    20 JONES        2975.00       5000        800       3000       2175    20 FORD         3000.00       5000        800       3000       2200    20 ADAMS        1100.00       5000        800       3000        300    20 SMITH         800.00       5000        800       3000          0    20 SCOTT        3000.00       5000        800       3000       2200    30 WARD         1250.00       5000        950       2850        300    30 TURNER       1500.00       5000        950       2850        550    30 ALLEN        1600.00       5000        950       2850        650    30 JAMES         950.00       5000        950       2850          0    30 BLAKE        2850.00       5000        950       2850       1900    30 MARTIN       1250.00       5000        950       2850        30014 rows selected

0 0