【Oracle数据库】分组函数

分组函数概述

SELECT [column,] group_function(column)FROM table[WHERE condition][GROUP BY column][HAVING group_function(column)exPRession[ORDER BY column|group_function(column)expression]
分组函数
MIN函数和MAX函数
MIN([DISTINCT|ALL] column|expression)：返回每组中的最小值MAX([DISTINCT|ALL] column|expression)：返回每组中的最大值MIN和MAX函数可以用于任何数据类型
-- 查询入职日期最早和最晚的日期SELECT MIN(hiredate),MAX(hiredate)FROM emp
SUM函数和AVG函数
SUM([DISTINCT|ALL] column|expression)：返回每组的总和AVG([DISTINCT|ALL] column|expression)：返回每组的平均值SUM和AVG函数只能够对数值类型的列或表达式操作
-- 查询职位以SALES开头的所有员工的平均工资、工资和SELECT AVG(sal),SUM(sal)FROM empWHERE job LIKE 'SALES%'
COUNT函数
COUNT(*)：返回表中满足条件的行记录数
-- 查询部门30有多少个员工SELECT COUNT(*)FROM empWHERE deptno=30COUNT([DISTINCT|ALL] column|expression)：返回表中满足条件的非空行记录数
-- 查询部门30有多少个员工领取奖金SELECT COUNT(comm)FROM empWHERE deptno=30
分组函数中的DISTINCT
DISTINCT会消除重复记录后再使用组函数
-- 查询有员工的部门数量SELECT COUNT(DISTINCT deptno)FROM emp
分组函数中的空值处理
除了COUNT(*)之外，其他所有分组函数都会忽略列表中的空值，然后再进行计算
-- 查询奖金的平均值SELECT AVG(comm)FROM emp在分组函数中使用NVL函数可以使分组函数强制包含含有空值的记录
-- 查询所有员工领取到的奖金的平均值SELECT AVG(NVL(comm,0))FROM emp
GROUP BY子句
创建数据组
使用GROUP BY子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组，其中GROUP BY子句指定要分组的列
-- 用GROUP BY子句创建数据组SELECT column,group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][ORDER BY column]
使用GROUP BY子句
在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY子句中
SELECT deptno,AVG(sal)FROM empGROUP BY deptnoGROUP BY所指定的列并不是必须出现在SELECT列表中
SELECT AVG(sal)FROM empGROUP BY deptno按多列分组的GROUP BY子句
SELECT deptno,job,SUM(sal)FROM empGROUP BY deptno,job
HAVING子句
不能在WHERE子句中限制组，要在HAVING子句中对数据组进行限制
-- 用HAVNG子句限制数据组SELECT column,group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column]使用HAVING子句限制数据组
SELECT deptno,MAX(sal)FROM empGROUP BY deptnoHAVING MAX(sal)>2900
SELECT语句执行过程
通过FROM子句中找到需要查询的表通过WHERE子句进行非分组函数筛选判断通过GROUP BY子句完成分组操作通过HAVING子句完成组函数筛选操作通过SELECT子句选择显示的列或表达式及组函数通过ORDER BY子句进行排序操作
组函数的嵌套
与单行函数不同，组函数只能嵌套两层
SELECT MAX(AVG(sal))FROM empGROUP BY deptno