Oracle 分组函数

2022-11-17 08:37:43

分组函数的介绍

分组函数作用于一组数据，并对一组数据返回一个值.

（引用网上的一张图）

分组函数的使用规则

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子句进行排序操作

码农公寓