|
|
51CTO旗下网站
|
|
移动端

超详细的5个Oracle数据库分组函数总结

今天主要分享下Oracle数据库分组函数group by 、 rollup、cude、grouping 、grouping sets的常用用法,以下以Oracle自带schema做演示。

作者:波波说运维来源:今日头条|2019-11-05 14:20

【线上直播】11月21日晚8点贝壳技术总监侯圣文《数据安全之数据库安全黄金法则》

概述

今天主要分享下Oracle数据库分组函数group by 、 rollup、cude、grouping 、grouping sets的常用用法,以下以Oracle自带schema做演示。

1、group by的使用

--根据DEPTNO和JOB进行分组。求相同DEPTNO,相同JOB的员工工资总和。

  1. SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY E.DEPTNO,E.JOB ORDER BY E.DEPTNO; 
超详细的5个Oracle数据库分组函数总结

2、group by 配合rollup的使用

rollup()--可以使用一个或者多个参数。意思是从右向左进行数据的汇总统计,并生成一行,rollup是个统计函数。

以下是根据分组情况进行统计,最终进行全部汇总。

(1)简单的使用rollup--生成一行新数据。(要生成新的一行数据,还可以使用UNION ALL)

  1. SELECT D.DUMMY FROM DUAL D GROUP BY ROLLUP(D.DUMMY); 

超详细的5个Oracle数据库分组函数总结

(2)先根据E.DEPTNO,E.JOB进行分组,然后从右向左

  1. SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY ROLLUP(E.DEPTNO,E.JOB) ORDER BY E.DEPTNO; 

超详细的5个Oracle数据库分组函数总结

针对以上的使用ROLLUP的结果的理解:

a:首先根据GROUP BY E.DEPTNO,E.JOB查询出9条数据(除4,8,12,13外),在根据rollup的定义,从右向左,对ROLLUP中的参数进行小计

首先根据JOB(对所有的JOB进行汇总),汇总出4,8,12行,在根据E.DEPTNO(对所有的DEPTNO进行汇总),汇总出第十三行数据。

(3) 特殊情况

  1. SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY ROLLUP(E.JOB,E.DEPTNO) ORDER BY E.DEPTNO; 

超详细的5个Oracle数据库分组函数总结

理解:首先根据GROUP BY E.DEPTNO,E.JOB查询出前九条数据,其次对E.DEPTNO进行汇总,但是必须考虑JOB,也就是相同的JOB,的所有的工资总和,所以出现下面五条数据。

3、group by 配合cube的使用

  1. SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY CUBE(E.DEPTNO,E.JOB) ORDER BY E.DEPTNO; 

超详细的5个Oracle数据库分组函数总结

理解:CUBE会对条件中的每一个条件进行单独的汇总:即对单独的列进行汇总

GROUP BY CUBE(E.DEPTNO,E.JOB)首先根据:GROUP BY E.DEPTNO,E.JOB查询数据,其次对E.JOB进行汇总(不考虑DEPTNO,单独汇总,而ROLLUP是在同一个DEPTNO下面)再对E.DEPTNO进行汇总,最后全部汇总。

  1. cube(a,b) 统计列包含:(a,b)、(a)、(b)、() 
  2. cube(a,b,c) 统计列包含:(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、() 

4、GROUPING 的使用

GROUPING函数可以接受一列,返回0或者1。如果列值为空,那么GROUPING()返回1;如果列值非空,那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时,GROUPING()就非常有用。

  1. SELECT GROUPING(E.DEPTNO), E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY ROLLUP(E.DEPTNO,E.JOB) ORDER BY E.DEPTNO; 

超详细的5个Oracle数据库分组函数总结

可以使用decode或者case函数进行转换这种不友好的显示:

  1. SELECT  
  2. CASE WHEN grouping(E.DEPTNO) = 1 
  3. THEN '总计' 
  4. ELSE E.DEPTNO || '' 
  5. END AS 部门, 
  6. CASE WHEN grouping(E.JOB) = 1 AND grouping(E.DEPTNO) = 0 
  7. THEN '小计' 
  8. ELSE E.JOB 
  9. END AS 工作种类, 
  10. SUM(E.SAL) FROM EMP E GROUP BY ROLLUP(E.DEPTNO,E.JOB) 
  11. ORDER BY E.DEPTNO; 
超详细的5个Oracle数据库分组函数总结
  1. SELECT DECODE(GROUPING(E.DEPTNO), 1, '总计', E.DEPTNO) AS 部门, 
  2. CASE 
  3. WHEN GROUPING(E.JOB) = 1 AND GROUPING(E.DEPTNO) = 0 THEN 
  4. '小计' 
  5. ELSE 
  6. E.JOB 
  7. END AS 工作种类, 
  8. SUM(E.SAL) 
  9. FROM EMP E 
  10. GROUP BY ROLLUP(E.DEPTNO, E.JOB) 
  11. ORDER BY E.DEPTNO; 

超详细的5个Oracle数据库分组函数总结

5、grouping sets提供了指定汇总集合条件的功能

根据E.DEPTNO,E.JOB分别汇总数据。

  1. SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY GROUPING SETS(E.DEPTNO,E.JOB); 

超详细的5个Oracle数据库分组函数总结


【编辑推荐】

  1. 详解Oracle11g和12c的v$pwfile_user视图及区别
  2. 分享几个常用的Oracle 11g内存视图脚本
  3. 11月数据库排行:排名前三数据库分数暴跌
  4. 详解MySQL数据库Innodb内存结构和其是如何使用内存的?
  5. 详解MySQL数据库删除所有表的外键约束、禁用外键约束相关脚本
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

骨干网与数据中心建设案例

骨干网与数据中心建设案例

高级网工必会
共20章 | 捷哥CCIE

374人订阅学习

中间件安全防护攻略

中间件安全防护攻略

4类安全防护
共4章 | hack_man

136人订阅学习

CentOS 8 全新学习术

CentOS 8 全新学习术

CentOS 8 正式发布
共16章 | UbuntuServer

282人订阅学习

读 书 +更多

非常网管——网络工程案例

本书面向企业网络应用需求,详细介绍了Windows网络互联解决方案、中小企业共享上网解决方案、基于ISA Server 2006的代理服务器与防火墙解决...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微