深入讲解如何使用rollup配合group by命令


Oracle数据库中的rollup配合group by命令使用,可以提供信息汇总功能(与"小计"相似)

  示例如下:

  SQL> select job,deptno,sal from emp;
  JOB DEPTNO SAL
  --------- --------- ---------
  CLERK 20 800
  SALESMAN 30 1600
  SALESMAN 30 1250
  MANAGER 20 2975
  SALESMAN 30 1250
  MANAGER 30 2850
  MANAGER 10 2450
  ANALYST 20 3000
  PRESIDENT 10 5000
  SALESMAN 30 1500
  CLERK 20 1100
  CLERK 30 950
  ANALYST 20 3000
  CLERK 10 1300

  已选择14行。

  SQL> select job,deptno,sum(sal) total_sal
  from emp group by rollup(job,deptno);
  JOB DEPTNO TOTAL_SAL
  --------- --------- ---------
  ANALYST 20 6000
  ANALYST 6000
  CLERK 10 1300
  CLERK 20 1900
  CLERK 30 950
  CLERK 4150
  MANAGER 10 2450
  MANAGER 20 2975
  MANAGER 30 2850
  MANAGER 8275
  PRESIDENT 10 5000
  PRESIDENT 5000
  SALESMAN 30 5600
  SALESMAN 5600
  29025

  已选择15行。

  SQL>


« 
» 
快速导航

Copyright © 2016 phpStudy | 豫ICP备2021030365号-3