一起聊聊Oracle高级查询（实例详解）

本篇文章给大家带来了关于Oracle高级查询的相关知识，其中包括了分组查询、多表查询和子查询，希望对大家有帮助。

Oracle高级查询

高级查询在数据库的开发过程中应用广泛，从分组查询、多表查询和子查询三个方面介绍Oracle的高级查询。

分组查询

分组查询是按照一定的规则进行分组，分组以后数据会聚合，需要使用聚合函数，但是使用聚合函数不一定要分组，分组的关键字是group by。

常用的聚合函数有：最大值max()，最小值min()，平均值avg()，总和sum()，统计个数count()

count函数使用列名时会自动忽略空值

nvl函数可以防止count自动忽略空值，它的作用是当comm为空时返回0，因为0是非空，所以会进入统计总数。

group by子查询

在select 列表中所有未包含在聚合函数中的列都应该包含在group by子句中。

单列分组

求每个部门的平均工资，显示部门号，部门的平均工资。

select deptno,avg(sal) from emp group by deptno order by deptno

多列分组

按部门，不同职位，统计员工的工资总和

select detpno,job,sum(sal) from emp group by deptno,job order by deptno

过滤分组

having子句的使用

where与having的区别

• where子句中不能使用聚合函数，先过滤后分组
• having子句中可以使用聚合函数，先分组后过滤

注意：从SQL优化的角度上看，尽量使用where，因为where使得分组记录数大大降低，从而提高效率。

求平均工资大于2000的部门

select deptno,avg(sal) from emp where(avg(sal)) > 2000 group by deptno

where子句中不能使用聚合函数，所以报错，改成having xxx子句即可。

select deptno,avg(sal) from emp group by deptno having(avg(sal)) > 2000

在分组查询中使用order by

求每个部门的平均工资，显示部门号，部门的平均工资，按照工资升序排列。

select deptno,avg(sal) from emp group by deptno order by avg(sal)

也可以按列的别名排序

select deptno,avg(sal) avgsal from emp group by deptno order by avgsal

还可以按列的序号排序，平均工资是第2列

select deptno,avg(sal) from emp group by deptno order by 2

降序排列加上desc即可

select deptno,avg(sal) from emp group by deptno order by 2 desc

分组函数的嵌套

求部门平均工资的最大值

select max(avg(sal)) from emp group by deptno

group by语句增强

主要用在group by语句报表功能

每个部门，安装不同职位，求工资总和，部门小结，总结。

可以使用rollup函数

select deptno,job,sum(sal) from emp group by rollup(deptno,job)

再设置一下显示格式，break on deptno表示相同的部门号只显示一个，skip 1表示不同的部门号之间空1行。

完善报表显示

增加标题，页码等

ttitle col 15 ‘我的报表’ col 35 sql.pno

设置标题，空15列显示我的报表，然后空35列显示页码

col deptno heading 部门号

col job heading 职位

col sum(sal) heading 工资总额

以上3行设置列标题

break on deptno skip 1

设置显示格式，相同的部门号只显示一个，不同部门号之间空1行

将这些设置保存到一个sql文件（注意要改成ANSI编码，否则会出现乱码并且设置无效），然后通过get命令读取执行。再次执行查询语句，得到如下报表。如果出现了多页，为了显示美观，可以设置一页显示