Oracle 分组函数和 group by 分组语句

Oracle里的多行函数也组函数,例如AVG, COUNT, MAX, MIN, SUM操作的是一组数据,返回一个结果。

求和函数SUM

sum() 可以对指定列的各行求和

select sun(sal) from emp;  --将sal列里的数值全部相加

非空行函数 COUNT

count() 函数可以统计指定列的非空行数、如果要求不重复的个数,使用distinct

select count(*) from emp;
select count(distinct job) from emp;   --重复的行 只取一次

求平均数函数 AVG

select avg(sal) from emp;

求员工的平均奖金:

SQL> select sum(comm)/count(*) 方式一,  sum(comm)/count(comm) 方式二,  avg(comm) 方式三   from emp;

结果:方式一结果不同,方式二 和 方式三结果一样。

☆NULL空值:组函数都有自动滤空功能(忽略空值),所以:

SQL> select count(*), count(comm) from emp;  执行结果不相同。

如何屏蔽 组函数 的滤空功能:

SQL> select count(*), count(nvl(comm,0)) from emp; 

但是实际应用中,结果为14和结果为4都有可能对,看问题本身是否要求统计空值。

分组数据

group by 、按照group by 后给出的表达式,将from后面的table进行分组,针对每一个组使用组函数

比如查询公司里 “部门”的平均工资:

SELECT deptno,  avg(sal)FROM emp GROUP BY deptno;
--可以抽象成:select a, 组函数(x) from 表 group by a; 这样的格式。

注意:所有没有包含在组函数中的列,都必须在group by的后面出现。所以 select a, b 组函数(x) …… group by 应该写成group by a, b;没有b语法就会出错,不会执行SQL语句。但,反之可以。Group by abc;  c可以不出现在select语句中。

由于语法规定group by后面 就会出现有多列的情况,例如这条SQL语句:

select deptno, job, avg(sal)  from emp  group by deptno, job  order by 1;

SQL的语义:按部门,不同的职位统计平均工资。先按第一列分组,如果第一列相同,再按第二列分组。所以查询结果中,同一部门中没有重复的职位

使用group by的时候还需要注意的一点的是 ,where子句中不能使用“组函数”。可以使用 Having来过滤分组。

比如查找出平均薪水大于2000的部门, sql语句如下:

SELECT deptno, avg(sal) FROM emp GROUP BY deptno having avg(sal)>2000;

从功能上讲,where还having都可以将满足条件的结果过滤处理来,但是语法规定 where子句中不能使用“组函数” 所以上句中的having不可以使用where代替。

如果只是单纯的求某个部门的平均工资,那么使用where和having都可以(因为子句中没有使用组函数),这种情况下 我们应该优先使用 where 因为如果有分组的话,where是先过滤再分组,而having是先分组再过滤。当数据量庞大如1亿条,where优势明显。

庄朋龙
庄朋龙

一个爱生活的技术菜鸟

留下评论

您的电子邮箱地址不会被公开。 必填项已用 * 标注