oracle 入门第三天 聚合函数+分组+子查询+分页

聚合函数

概述

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

聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用，所以也把其它称之为分组函数或者组函数。根据翻译的不同而叫法不同，实则是一个东西，如果见到不用奇怪。

对于聚合函数而言 其分组 是按 group by 后面跟的列 进行分组 将相同内容的行 分成一组 当列数 为2及其以上上 将同行多列 都相等的内容 视为一组 。

小实现 （帮助理解）

select d.name
from s_emp s,s_dept d
where s.dept_id=d.id
group by  d.name;

通过group by 将 部门表中 根据部门名字进行分组

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

加上 count 统计每组里面的人数 sum 对该部门里面所有员工工资的集合 并且根据部门薪水的和进行倒叙排序

select d.name,count(*),sum(s.salary)
from s_emp s,s_dept d
where s.dept_id=d.id
group by  d.name
order by sum(s.salary) desc;

得到结果：

• 注 group by 子句就是根据一定的条件给数据进行分组

而分组完毕之后 就可以使用 聚合函数对分组之后的数据进行操作，常用的聚合函数有：

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

select语句执行顺序，和聚合函数书写规则

而聚合函数 可以写在哪里 又具有一定的规矩。

在一条完整的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 后面 （非常重要）

我们对上面这句话 进行解释 ，举个例子就能明白：

select d.name,count(*),sum(s.salary)
from s_emp s,s_dept d
where s.dept_id=d.id
group by  d.name
order by sum(s.salary) desc;

相对于这个语句，我还想要显示 员工的部门 id
则 select 变成
select d.id,d.name,count(*),sum(s.salary)
此时我们想要语句通过就需要 在group by 后面加上 d.id
此时的group by 语句变成：
group by d.name,d.id
此时语句通过。 这是对上一句话的解释。

但是注意 因为group by 是分组语句
此时分组已经变成 根据 d.name,d.id 俩者组成的共同列 去分组 只有这俩者都相同才会被分为一组
此时结果变为:

在表中 部门名字相同但 部门id 可能不同 ，因为不同的部门id 代表着不同的地区的部门 。 部门名字相同，但部门id不同，则说明 俩个部门在不同的地区，部门名字相同，职责相同，但不是同一个部门。

having解释

having 是跟在group 后面的条件语句
因为where 不能出现 聚合函数，所以使用having 来使用需要用到聚合函数条件的 语句。

注意俩者的区别：

where与having的对比：

• where和having都是做条件筛选的
• where执行时间比having 要早
• where后面不能出现聚合函数
• having 后面可以出现聚合函数
• where语句要紧跟from 语句后面
• having 语句要紧跟 group by语句后面

groub by 与having 的关系

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

order by 语句

• 如果sql语句中需要排序，那么就一定要写在sql语句的最后面
• order by 语句后面可以出现组函数 （例如之前的例子中利用 工资的和进行排序）

默认分组

在使用聚合函数的时候：

• 如果使用了group by 则对分组之后的数据进行操作
• 如果没有使用，则默认将所有数据当成一个分组，对所有数据进行操作

子查询（嵌套查询）

概述

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

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

直接上例子练习

---

练习1：
查询员工信息，这些员工的工资要比自己所在部门的平均工资高，同时显示部门的名称以及所在地区
第一步：求出各部门的平均工资及其对应的部门id

select s.dept_id,avg(s.salary)
from  s_emp s
group by s.dept_id;

第二步：将员工的部门id对应的平均工资与员工自身工资相比 大于的显示 (将第一步查询结果作为表格用于获取员工部门的平均工资) 得到员工信息

select s1* 
from s_emp s1,(
select s.dept_id did,avg(s.salary) sa
from  s_emp s
group by s.dept_id
) ags
where s1.dept_id =ags.did and s1.salary>ags.salary;

第三步： 根据员工信息获取 其所在的部门名字，及部门所在的区域名字

select s1* ,d.name,r.name
from s_emp s1,(
select s.dept_id did,avg(s.salary) sa
from  s_emp s
group by s.dept_id
) ags,s_dept d,s_region r
where s1.dept_id =ags.did and s1.salary>ags.salary
and s1.dept_id = d.id and d.region_id =r.id;

练习2：
查看和Ngao所在部门的区域id一样的所有员工id和名字

第一步  查询nago 所在区域id
select r.id
from s_emp s,s_dept d,s_region r
where s.dept_id=d.id and d.region_id = r.id
and s.last_name ='Ngao';
查询区域id 和 ngao一样的员工信息
select s.*,d.region_id
from s_emp s,s_dept d,s_region r
where s.dept_id=d.id and d.region_id = r.id
and r.id=(select r.id
from s_emp s,s_dept d,s_region r
where s.dept_id=d.id and d.region_id = r.id
and s.last_name ='Ngao');

练习3：查询每一个部门的员工小于该部门的平均工资的员工的信息

select s1.*,ags.avgs                
from  s_emp s1,(select avg(s.salary) 
avgs,s.dept_id did
from s_emp s
group by s.dept_id) ags
where s1.dept_id = ags.did and s1.salary<ags.avgs

分页

概述

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

在oracle 中 rownum 用来表示 表的行号

所有可以利用rownum 分割表 一次达到分页的目的

语句如下：

select da.*
from (select s.* , rownum rn 
from s_emp s
where rn <=100) da
where  rn between 20 and 40

就可以取到 第20条到第40条的数据