Oracle 分组函数-阿里云开发者社区

Oracle 分组函数

2023-05-05 73

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 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]

实例讲解：

create table stu_class(
sno number(2) not null primary key,
sname varchar2(20) not null,
ssex varchar2(2),
sdept varchar2(20),
sclass varchar2(10),
sscore number(5)
);

insert into stu_class values(1,'小明','男','信息系','软件2班',60);
insert into stu_class values(2,'小洪','女','信息系','软件1班',70);
insert into stu_class values(3,'小亮','男','电子系','机械1班',66);
insert into stu_class values(4,'小张','女','电子系','机械1班',27);
insert into stu_class values(5,'小王','男','信息系','软件1班',65);
insert into stu_class values(6,'小李','女','信息系','软件1班',64);
insert into stu_class values(7,'小狗','男','电子系','机械2班',60);
insert into stu_class values(8,'小猫','女','电子系','机械1班',88);
insert into stu_class values(9,'小汪','男','电子系','机械2班',89);

分组函数(函数的介绍和说明引用http://blog.csdn.net/rex90522/article/details/54910729)

(不加Group By语句的话只可在查询列显示分组函数，不可显示其他字段)

MIN函数和MAX函数

MIN([DISTINCT|ALL] column|expression)：返回每组中的最小值

MAX([DISTINCT|ALL] column|expression)：返回每组中的最大值

MIN和MAX函数可以用于任何数据类型

select max(sscore) from stu_class;
select min(sscore) from stu_class;
select max(sname) from stu_class;
select max(sdept) from stu_class;

SUM函数和AVG函数

SUM([DISTINCT|ALL] column|expression)：返回每组的总和

AVG([DISTINCT|ALL] column|expression)：返回每组的平均值

SUM和AVG函数只能够对数值类型的列或表达式操作

select sum(sscore) from stu_class;
select avg(sscore) from stu_class;
select avg(sscore) from stu_class where sdept='信息系;

COUNT函数

COUNT(*)：返回表中满足条件的行记录数

COUNT([DISTINCT|ALL] column|expression)：返回表中满足条件的非空行记录数

--当前表中的总记录数
select count(*) from stu_class;
--查询sclass这一列不为空时的总记录数
select count(sclass) from stu_class;

DISTINCT函数

DISTINCT会消除重复记录后再使用组函数

--显示有多少不同的班级空值也会统计出来
select distinct(sclass) from stu_class;
--统计有多少院系
select count(distinct(sdept)) from stu_class;

分组函数中的空值处理

除了COUNT(*)和DISTINCT(COLUMN)之外，其他所有分组函数都会忽略列表中的空值，然后再进行计算

在分组函数中使用NVL函数可以使分组函数强制包含含有空值的记录

select avg(sscore) from stu_class where sclass='软件2班';
--如果sscore列有空值则按0处理
select avg(nvl(sscore,0)) from stu_class where sclass='软件2班';

GROUP BY子句

创建数据组

使用GROUP BY子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组，其中GROUP BY子句指定要分组的列

SELECT [column,] group_function(column)  
FROM table  
[WHERE condition]  
[GROUP BY column]  
[HAVING group_function(column)expression  
[ORDER BY column|group_function(column)expression]

使用GROUP BY子句

在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY子句中

--按照班级分组后求每个班级的得分最高的值，如果想将sclass查出则需要在group by语句中指定按其进行分组得到每班最大值
select sclass,max(sscore) from stu_class group by sclass;

GROUP BY所指定的列并不是必须出现在SELECT列表中

--group by 所指定的列不一定要出现在查询列表中
select sclass,sum(sscore) from stu_class group by sdept,sclass;

按多列分组的GROUP BY子句

--按照多列进行分组查询求平均值
select sdept,ssex,avg(sscore) from stu_class group by sdept,ssex;

HAVING子句

不能在WHERE子句中限制分组信息，要在HAVING子句中对数据组进行限制

--查询所有女生中按照院系和班级排序后平均成绩大于60的相关信息
select sdept,sclass,avg(nvl(sscore,0)) from stu_class where ssex='女' group by sdept,sclass having avg(nvl(sscore,0))>60;

组函数的嵌套

与单行函数不同，组函数只能嵌套两层

--嵌套后就不能在查询列表中查分组函数意外的其他列了，否则会报错
select max(avg(sscore)) from stu_class group by sdept,sclass;

SELECT语句执行过程

<1>通过FROM子句中找到需要查询的表

<2>通过WHERE子句进行非分组函数筛选判断

<3>通过GROUP BY子句完成分组操作

<4>通过HAVING子句完成组函数筛选操作

<5>通过SELECT子句选择显示的列或表达式及组函数

<6>通过ORDER BY子句进行排序操作