分组函数

2
1

programming
1
Oracle SQL开发基础

2
2

programming
课程结构
内容

课时（H）

第一章 Oracle数据库基础

2.5

第二章 编写简单的查询语句

1.5

第三章 限制数据和对数据排序

2

第四章 单行函数

4

第五章 多表查询

4

第六章 分组函数

3

第七章 子查询

4

第八章 数据操作与事务控制

4

第九章 表和约束

4

第十章 其他数据库对象

4

 

2
3

programming
第六章 分组函数

目标:
本章旨在向学员介绍：
1) 分组函数的基本概念
2）分组函数的使用方法
时间： 3学时
教学方法：讲授ppt＋上机练习
2
4

programming
本章要点
.分组函数的目的
.分组函数基本概念
.SUM、AVG、MIN、MAX、COUNT函数使用
.GROUP BY子句
.HAVING子句

2
5

programming
第六章 分组函数

分组函数:
6.1分组函数
6.2GROUP BY…HAVING子句的使用
2
6

programming
6.1分组函数

.分组函数是对表中一组记录进行操作，每组只返回一个结果。
即首先要对表记录进行分组，然后再进行操作汇总，每组返回
一个结果。分组时可能是整个表分为一组，也可能根据条件分
成多组。
.分组函数常用到以下的五个函数：
–MIN
–MAX
–SUM
–AVG
–COUNT

 

2
7

programming
6.1.1MIN函数和MAX函数 1/2
.MIN和MAX函数主要是返回每组的最小值和最大值。
–MIN([DISTINCT|ALL]表达式)
–MAX([DISTINCT|ALL]表达式)

.例6-1 员工最低工资及最高工资的示例。

SELECT MIN(salary), MAX(salary)
FROM employees;
2
8

programming
6.1.1MIN函数和MAX函数 2/2

.例6-2 员工姓最开始及最后的示例。
.例6-3 员工最低工资及最高工资的示例 。

SELECT MIN(last_name), MAX(last_name)

FROM employees;

SELECT MIN(salary), MAX(salary)
FROM employees;
2
9

programming
6.1.2SUM函数和AVG函数
.SUM和AVG函数分别返回总和及平均值。
–SUM([DISTINCT|ALL]n)
–AVG([DISTINCT|ALL]n)

.SUM和AVG函数都是只能够对数字类型的列或表达式操作。
.例6-4 公司员工总工资及平均工资的示例。

SELECT SUM(salary), AVG(salary)
FROM employees;
2
10

programming
6.1.3COUNT函数

.COUNT函数的主要功能是返回每组记录的条数。
–COUNT({*|[DISTINCT|ALL]表达式})

.例6-5 公司IT_PROG职位的员工人数的示例。
.例6-6 公司有部门员工人数的示例。

SELECT COUNT(*)

FROM employees

WHERE job_id='IT_PROG';

SELECT COUNT(department_id)
FROM employees;
2
11

programming
6.1.4组函数中DISTINCT
.DISTINCT会消除重复记录后再使用组函数
.例6-7 公司有员工部门数的示例。

SELECT COUNT(DISTINCT department_id)
FROM employees;
2
12

programming
6.1.5组函数中空值处理

.所有组函数对空值都是忽略的。
.例6-8 员工平均奖金的示例——忽略空值。
.例6-9 员工平均奖金的示例——空值转化。

SELECT AVG(commission_pct)

FROM employees;

SELECT AVG(NVL(commission_pct,0))
FROM employees;
2
13

programming
第六章 分组函数
分组函数:
6.1分组函数
6.2GROUP BY…HAVING子句的使用
2
14

programming
6.2.1GROUP BY子句 1/2
.组函数忽略空值,可以使用NVL,NVL2,COALESCE 函数处
理空值
.结果集隐式按升序排列,如果需要改变排序方式可以使用
Order by 子句(9i)

SELECT 列名, 组函数(列名)
FROM 表名
[WHERE 条件]
[GROUP BY 分组列]
[ORDER BY 列名];
2
15

programming
6.2.1GROUP BY子句 2/2

.例6-10 每个部门的总工资。
.例6-11 相同职位且经理相同的员工平均工资。

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id ;
SELECT job_id,manager_id, AVG(salary)
FROM employees
GROUP BY job_id,manager_id
ORDER BY job_id;
2
16

programming
6.2.2GROUP BY子句注意问题 1/2
.在GROUP BY子句使用中，有两点需要注意：
–GROUP BY子句后的列可以不在SELECT语句中出现。
–SELECT子句中出现的非分组函数列必须在GROUP BY子句
中出现。

.例6-12 查询公司每个职位的平均工资，职位列不显示，同时结
果按照平均工资排序。

SELECT AVG(salary)
FROM employees
GROUP BY job_id
ORDER BY AVG(salary);
2
17

programming
6.2.2GROUP BY子句注意问题 2/2

.例6-13 分组汇总错误示例
.例6-14 分组汇总正确示例

 

SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id;
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id,job_id;
2
18

programming
6.2.3HAVING子句 1/5
.例6-15 组函数筛选示例。
.原因是Oracle查询语句的执行顺序是：
–FROM WHERE GROUP BY SELECT ORDER BY

 

SELECT job_id, MAX(salary)
FROM employees
WHERE MAX(salary)>=9000
GROUP BY job_id;
2
19

programming
6.2.3HAVING子句 2/5

.语法结构如下：

SELECT 列名, 组函数
FROM 表名
[WHERE 条件]
[GROUP BY 分组列]
[HAVING 组函数表达式]
[ORDER BY 列名];
2
20

programming
6.2.3HAVING子句 3/5
.例6-16 组函数筛选示例。

SELECT job_id, MAX(salary)
FROM employees
GROUP BY job_id
HAVING MAX(salary)>=9000;
2
21

programming
6.2.3HAVING子句 4/5
.总结SELECT语句执行过程：
–通过FROM子句中找到需要查询的表；
–通过WHERE子句进行非分组函数筛选判断；
–通过GROUP BY子句完成分组操作；
–通过HAVING子句完成组函数筛选判断；
–通过SELECT子句选择显示的列或表达式及组函数；
–通过ORDER BY子句进行排序操作。

 

2
22

programming
6.2.3HAVING子句 5/5
.例6-17 组函数演示。

SELECT department_id, MAX(salary)
FROM employees
WHERE department_id BETWEEN 30 AND 90
GROUP BY department_id
HAVING MAX(salary)>=9000
ORDER BY MAX(salary);
2
23

programming
6.2.4组函数的嵌套
.组函数可以实现嵌套操作，嵌套级数是2级(select)。
.例6-18 组函数嵌套演示。

SELECT MAX(COUNT(employee_id))
FROM employees
GROUP BY department_id;
2
24

programming
本章小结
.MIN函数和MAX函数
.SUM函数和AVG函数
.COUNT函数
.组函数中DISTINCT消除重复行
.组函数中空值处理
.通过GROUP BY子句进行分组汇总
.GROUP BY子句使用需要注意的两个问题
.HAVING子句的使用
.组函数的嵌套

2
25

programming
练习
.1.查询部门平均工资在8000元以上的部门名称及平均工资。
.2.查询员工工作编号中不是以“SA_”开头并且平均工资在
8000元以上的工作编号及平均工资，并按平均工资

 降序排序。

.3.查询部门人数在4人以上的部门的部门名称及最低工

 资和最高工资。

.4.查询工作不为SA_REP，工资的和大于等于25000的工作编
号和每种工作工资的和。
.5.显示经理号码，这个经理所管理员工的最低工资，不包括经
理号为空的，不包括最低工资小于3000的，按最低工资由高到
低排序。

2
26

programming
谢谢