聚合函数
概念
聚合函数，一般会操作多组数据，并对每一组中的某个列，执行计算并返回单一的值。
聚合函数经常与SELECT语句的GROUP BY子句一同使用，所以也把其它称之为分组函数。
select last_name,dept_id
from s_emp
order by dept_id;
LAST_NAME DEPT_ID
--------------- ----------
Quick-To-See 10
Magee 31
Nagayama 31
Giljum 32
Sedeghi 33
Nguyen 34
Patel 34
Dumas 35
Urguhart 41
Ngao 41
Smith 41
可以看出，部门id的员工划分在同一块，可以通过这一特性进行分组
-- 对数据进行分组，按照dept_id相同的员工是一个组，进行划分
select dept_id,count(*),sum(salary)
from s_emp
group by dept_id
DEPT_ID COUNT(*) SUM(SALARY)
---------- ---------- -----------
42 3 3245
43 3 2700
34 2 2320
44 2 2100
31 2 2800
32 1 1490
35 1 1450
50 2 4050
41 4 4990
45 3 3267
33 1 1515
DEPT_ID COUNT(*) SUM(SALARY)
---------- ---------- -----------
10 1 1450
常用的聚合函数有：
- avg，求平均值count，计算有多少条数据
- max，求最大值
- min，求最小值
- sum，求和
执行顺序
现在一个select查询语句，由以下几部分组成，并且按照数字的大小进行执行的顺序
select 字段1,字段2 -- 5
from 表 -- 1
where 条件 -- 2
group by 分组条件 -- 3
having 分组筛选条件 -- 4
order by 排序条件 -- 6
聚合函数能够出现的位置：
- select后面
- having后面
- order by后面
注意，where后面一定【不能】出现组函数（聚合函数）
注意，如果select、having语句后面出现了组函数，那么select、having后面没有被组函数修饰的列，就必须出现在group by 后面（非常重要）
默认分组
在使用聚合函数的时候：
- 如果还使用了group by分组，那么就表示先分组，然后对每一个小组使用聚合函数
- 如果没有使用group by分组，那么就表示全部数据是一个默认小组，然后对这个全部数据使用聚合函数
例如，查询s_emp表中所有员工的平均工资
select avg(salary)
from s_emp;
AVG(SALARY)
-----------
1255.08
group by
select dept_id,count(*),min(salary),max(salary),sum(salary),avg(salary)
from s_emp
group by dept_id;
DEPT_ID COUNT(*) MIN(SALARY) MAX(SALARY) SUM(SALARY) AVG(SALARY)
---------- ---------- ----------- ----------- ----------- -----------
42 3 795 1250 3245 1081.66667
43 3 750 1100 2700 900
34 2 795 1525 2320 1160
44 2 800 1300 2100 1050
31 2 1400 1400 2800 1400
32 1 1490 1490 1490 1490
35 1 1450 1450 1450 1450
50 2 1550 2500 4050 2025
41 4 940 1450 4990 1247.5
45 3 860 1307 3267 1089
33 1 1515 1515 1515 1515
DEPT_ID COUNT(*) MIN(SALARY) MAX(SALARY) SUM(SALARY) AVG(SALARY)
---------- ---------- ----------- ----------- ----------- -----------
10 1 1450 1450 1450 1450
having
查询s_emp表中部门的平均工资大于等于1400的部门
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>=1400;
DEPT_ID AVG(SALARY)
---------- -----------
31 1400
32 1490
35 1450
50 2025
33 1515
10 1450
查询s_emp表中部门的总工资大于等于4000的部门
select dept_id,sum(salary)
from s_emp
group by dept_id
having sum(salary)>=4000;
DEPT_ID SUM(SALARY)
---------- -----------
50 4050
41 4990
子查询
概述
子查询，也称嵌套查询，即一个select语句中嵌套了另外的一个或者多个select语句
子查询的思路就是，把第一个sql语句的查询结果，在第二个sql语句中使用，这时候第一条sql语句的结果，在第二条sql中就可以充当一个where条件中的一个值，或者充当一张虚拟的表。
使用
查询工资比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'
);
LAST_NAME SALARY
--------------- ----------
Velasquez 2500
Ngao 1450
Nagayama 1400
Quick-To-See 1450
Ropeburn 1550
Urguhart 1200
Menchu 1250
Biri 1100
Catchpole 1300
Havel 1307
Magee 1400
查询平均工资比41号部门的平均工资高的部门中员工的信息
-- 第一步，查询41号部门的平均工资
select avg(salary)
from s_emp
where dept_id=41;
-- 第二步，查询平均工资比1247.5高的部门编号
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5;
-- 第三步，查询平均工资比1247.5高的部门中员工信息
select last_name,salary,dept_id
from s_emp
where dept_id in(10,31,32,33,35,50);
-- 第四步，把in条件后的数据进行替换
select last_name,salary,dept_id
from s_emp
where dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5
);
-- 第五步，把1247.5进行替换
select last_name,salary,dept_id
from s_emp
where dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp
where dept_id=41
)
);
查询平均工资比41号部门的平均工资高的部门中员工的信息，并且显示出当前部门的平均工资
-- 第一步，查询平均工资比41号部门的平均工资高的部门中员工的信息
select last_name,salary,dept_id
from s_emp
where dept_id in(
select dept_idfrom s_emp
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp
where dept_id=41
)
);
-- 第二步，查询每个部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id;
-- 第三步，把第二步的结果当做一个虚拟的表，在第一步的sql语句中，进行多表查询，等值连接的条件是俩张表的dept_id相等
select se.last_name,se.salary,se.dept_id,temp.avgSal
from s_emp se,(
select dept_id,avg(salary) avgSal
from s_emp
group by dept_id) temp
where se.dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp
where dept_id=41
)
) and se.dept_id = temp.dept_id;
分页查询
概述
实现分页的方式有很多，并且不同数据库，实现分页查询的方式也会不同，那么在Oracle中，可以使用伪列rownum，再结合子查询来进行简单的实现。
实现
rownum表示为一个查询的结果的行号，从1开始，连续不断
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表中的第610条数据，并且可以通过控制俩个参数，查询出表中任意的第mn条数据
思路：我们也可以通过minus方式 ，将第一个结果集除去第二个结果集和它相同的部分
网友评论