oracle3 聚合函数、分组、子查询、分页

1. 聚合函数

1.1 概述

聚合函数，一般会操作多组数据，并对每一组中的某个列，执行计算并返回单一的值。

聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用，所以也把其它称之为分组函数。

在查询数据的时候，经常会把数据按照某一个条件进行分组，例如

select last_name,dept_id
from s_emp
order by dept_id;

可以看出，一共查询出了25条数据，但其实可以把部门编号相同的员工，归为一组，这样就得到 了若干小组

分组完成后，就可以使用聚合函数（组函数），来统计每组中员工的一些信息了。

例如，查看每个部门员工的人数和工资总和

select dept_id,count(*),sum(salary)
from s_emp
group by dept_id
order by dept_id;

group by 子句就是按照某一个条件，给数据进行分组

常见的聚合函数有：

• avg ，求平均值
• count ，计算有多少条数据
• max ，求最大值
• min ，求最小值
• sum ，求和

1.2 执行顺序

一个完整的select语句，是有自己的执行顺序的。

现在一个select查询语句，由以下几部分组成：

select 字段1,字段2
from 表
where 条件
group by 分组条件
having 分组筛选条件
order by 排序条件

select语句执行顺序：

1. from子句，组装来自表的数据，有可能是多张表
2. where子句，基于指定的条件对记录行进行筛选
3. group by子句，将数据划分为多个分组
4. 使用聚合函数对每个小组中的数据进行计算
5. having子句，进行条件筛选，这里可以使用聚合函数的计算结果
6. 计算所有的运算表达式，主要是select部分
7. order by子句，对结果集进行排序

聚合函数能够出现的位置：

1. select后面
2. having后面
3. order by后面

where 后面一定不能出现组函数

如果select、having语句后面出现了组函数，那么select、having后面没有被组函数修饰的 列，就必须出现在group by 后面 （非常重要）

where和having对比：

1. where和having都是做条件筛选的
2. where执行的时间比having要早
3. where后面不能出现组函数
4. having后面可以出现组函数
5. where语句要紧跟from后面
6. having语句要紧跟group by后面

group by和having的关系：

1. group by可以单独存在，后面可以不出现having语句
2. having不能单独存在，如果需要出现，那么就必须出现在group by后面

order by语句：

1. 如果sql语句中需要排序，那么就一定要写在sql语句的最后面
2. order by后也可以出现组函数

1.3 默认分组

在使用聚合函数的时候：

• 如果还使用了group by分组，那么就表示先分组，然后对每一个小组使用聚合函数
• 如果没有使用group by分组，那么就表示全部数据是一个默认小组，然后对这个全部数据使用聚合 函数

1.4 group by

例如，统计每个部门中，每个职位的人数有多少（相同部门、相同职位的人数统计）

select
	dept_id,title,count(*)
from
	s_emp
group by
	dept_id,title
order by 
	dept_id;

可以看出，这时候是把dept_id和title相同的数据，放到了一个组中，就表示统计每个部门每个职 位的人数

1.5 having

例如，查询s_emp表中部门的平均工资大于等于1400的部门

select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>=1400;

例如，查询s_emp表中部门的总工资大于等于4000的部门

select dept_id,sum(salary)
from s_emp
group by dept_id
having sum(salary)>=4000;

1.6 案例

例如，查询s_emp表中，部门的平均工资大于等于1400的部门，并且显示出这些部门的名字，同时按照 部门编号进行排序

第一步，查询出基本需求

select
	dept_id,avg(salary)
from
	s_emp
group by
	dept_id
having
	avg(salary)>=1400
order by
	dept_id;

第二步，加入多表查询，并且分别给表起别名

select
	se.dept_id,avg(se.salary)
from
	s_emp se,s_dept sd
group by
	se.dept_id
having
	avg(se.salary)>=1400
order by
	se.dept_id;

第三步，查询出s_dept表中的部门名称，并且进行等值连接

select
	se.dept_id,avg(se.salary),sd.name
from
	s_emp se,s_dept sd
where
	se.dept_id = sd.id
group by
	se.dept_id
having
	avg(se.salary)>=1400
order by
	se.dept_id;

第四步，select语句后出现了组函数，那么没有被组函数修饰的列，需要放到group by分组后面

select
	se.dept_id,avg(se.salary),sd.name
from
	s_emp se,s_dept sd
where
	se.dept_id = sd.id
group by
	se.dept_id,sd.name
having
	avg(se.salary)>=1400
order by
	se.dept_id;

2. 子查询

2.1 概述

子查询，也称嵌套查询，即一个select语句中嵌套了另外的一个或者多个select语句

子查询的思路就是，把第一个sql语句的查询结果，在第二个sql语句中使用，这时候第一条sql语句的结 果，在第二条sql中就可以充当一个where条件中的一个值，或者充当一张虚拟的表。

2.2 使用

例如，查询工资比Smith工资高的员工信息

第一步，查询Smith的工资数

select salary
from s_emp
where last_name='Smith';

第二步，查询工资比940高的员工信息

select last_name,salary
from s_emp
where salary>940;

第三步，把第二步中的数字940替换成第一步中的sql语句即可，可以使用小括号包裹子查询

select last_name,salary
from s_emp
where salary>(
select salary
from s_emp
where last_name='Smith'
);

3. 分页查询

3.1 概述

分页查询，是项目中常用的功能。

因为表中数据过多，我们并不能一次全都查询出来交给前端去展示，而是需要每次按照提前设置好的要 求，查询一部分数据给用户，当用户点击下一页的时候，再查出下一部分数据给用户。

实现分页的方式有很多，并且不同数据库，实现分页查询的方式也会不同，那么在Oracle中，可以使用 伪列rownum，再结合子查询来进行简单的实现。

3.2 实现

rownum表示为一个查询的结果的行号，从1开始，连续不断

例如，查询s_emp表中，前10条数据

select rownum rn,id,last_name,dept_id
from s_emp
where rownum<=10

rownum当做条件查询时，只能：

• 等于1
• 大于0
• 小于任何数

但是，在这sql中，给rownum起一个别名rn，然后把这个sql语句作为第二条sql语句的子查询，并且把它 当做一张虚拟的表，那么这时候，这个虚拟表中的rn是可以当做普通的字段进行操作了

select t.id,t.last_name,t.dept_id
from (
select rownum rn,id,last_name,dept_id
from s_emp
where rownum<=10
) t
where t.rn>=6;

可以看出，这时候就查询出来s_emp表中的第6~10条数据，并且可以通过控制俩个参数，查询出 表中任意的第m~n条数据

例如，查询s_emp表中，第3~7条数据

select t.id,t.last_name,t.dept_id
from (
select rownum rn,id,last_name,dept_id
from s_emp
where rownum<=7
) t
where t.rn>=3;