Oracle-第6章 分组函数

第6章 分组函数内容

6.1 分组函数概述
6.2 count,sum,avg,max,min分组函数
6.3 group by和having子句
6.1 分组函数概述
  分组函数也叫统计函数，一般用于数据统计使用
  分组函数是对数据行的集合进行操作并按组给出一个结果，这个结果可直接输出，或者用来做判断条件。
  分组函数是对表中一组记录进行操作，每组只返回一个结果，即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果，分组时可能是整个表分为一组，也可能根据条件分成多组。其对数据集合处理的函数，可以处理多行数据。
  分组查询必须设定分组条件，根据分组条件进行统计。组函数是对分组后的集合进行计算，按分组条件分组后的数据应该有多条，使用分组后的函数才有意义

  例1：查询出每个部门的最高工资

Ans: select deptno,max(sal) from emp
       group by deptno;

6.2 count,sum,avg,max,min分组函数

a). count函数的主要功能是返回满足条件的每组记录条数。
b). sum和avg函数分别返回每组的总和及平均值, 都是只能够对数值类型的列或表达式操作。
c). min和max函数主要是返回每组的最小值和最大值，并且可以用于任何数据类型。并且，min和max统计字符类型时，会跟进字符顺序进行统计。

1.查询10号部门的最高工资，最低工资，平均工资。

select max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资 from emp where deptno=10;

2.查询入职日期最早和最晚的日期

select max(hiredate),min(hiredate) from emp;

3.查询入职日期在1981年6月后的员工的数量，及平均工资。

select count(empno) 员工数量,avg(sal) 平均工资 from emp where hiredate>=to_date('6月-1981','mon-yyyy');

4.查询出公司每月的工资支出是多少

select sum(sal) 每月工资总和,substr(to_char(hiredate,'dd-mm-yyyy'),4,2) 月份 from emp 
       group by substr(to_char(hiredate,'dd-mm-yyyy'),4,2) 
       order by 月份;理解错题目，入职日期并不是发工资时间
select sum(sal) from emp;正确答案

5.公司员工普提工资上调50%，查询工资上调前后，每月的工资支出。

select sum(sal) 上调前，sum(sal*(1+0.5)) 上调后 from emp;
d).其中，组函数中distinct会消除重复记录后再使用组函数。
  例：
  查询所有员工的部门数量
select count(deptno) from emp;错误
select count(distinct deptno) from emp;
e). 分组函数中空值处理, 除了COUNT（*）之外，其它所有分组函数都会忽略列中的空
值，然后再进行计算。但在分组函数中使用NVL函数强制包含含有空值的记录。
  例1:查询员工的平均奖金，没有奖金的视为0.

select avg(comm) from emp;
select avg(nvl(comm,0)) from emp;

6.3 group by和having子句

   通过 group by 子句可将表中满足where条件的记录按照指定的列划分成若干个小组,其中group by子句指定要分组的列。带group by的分组查询，执行顺序是先分组group by后查询 select。

  例1：查询每个部门的编号，平均工资。

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

  例2：查询工资最高的员工的姓名，入职日期，及工资

select ename, hiredate, sal
  from emp
 where sal = (select max(sal) from emp);

  分组列可以不在select子句中显示，但必须在group by 分组中。因为group by子句中的关系要和查询的语句一一对应，在某些情况下只是为了能让所查询的语句在select中显示。
  即：在select列表中除了分组函数那些项，所有列都必须包含在group by子句中。而group by所指定的列并不是出现在select列表中。
  例3：查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

select d.deptno 部门编号,
       d.dname 部门名称,
       count(e.empno) 部门人数,--查询员工表中的相同部门下的员工数
       max(e.sal) 最高工资,
       min(e.sal) 最低工资,
       sum(e.sal) 工资总和,
       avg(e.sal) 平均工资
  from emp e, dept d
 where e.deptno = d.deptno
 group by d.deptno, d.dname

   在上述中，因为需要查询dname的结果值，在分组查询中，分组列要在group by子句中出现，所以需要在group by子句中添加dname的值。而group by是根据deptno分组后，在根据deptno值相同的情况下通过dname值分组。

  例4：查询出每个部门的最高工资

Ans：select max(sal) from emp
       group by deptno;

  如果在查询中使用了组函数，任何不在组函数中的列或表达式都必须包含在GROUP BY子句中 。即在select子句中的非组函数列，都必须添加到group by分组中。

  例1：查询入职日期在1982年2月后的员工信息，每个部门的平均工资，并按照部门进行分组。

select deptno, avg(sal)
  from emp
 where hiredate >= to_date('1982-2', 'yyyy-mm')
 group by deptno;

  例2：查询每个部门每个岗位的工资总和

select deptno,job,sum(sal) from emp group by deptno,job;

不能在where子句中限制组，可以通过having子句限制组。使用having子句限制组的：
 记录已经分组
 使用过组函数
 与having子句匹配的结果才输出
  例5：查询每个部门最高工资大于2900的部门编号，最高工资

select deptno, max(sal) from emp group by deptno having max(sal) > 2900;

重点：
SELECT语句执行过程：
1.通过FROM子句中找到需要查询的表；
2.通过WHERE子句进行非分组函数筛选判断；
3.通过GROUP BY子句完成分组操作；
4.通过HAVING子句完成组函数筛选判断；
5.通过SELECT子句选择显示的列或表达式及组函数；
6.通过ORDER BY子句进行排序操作。
即sql执行顺序为：from–>where–>group by–>having–>select–order by。
例6：按入职年份进行统计和排序，查询出每各年份最高工资和最低工资。

select max(sal), min(sal), to_char(hiredate,'yyyy') year
  from emp
 group by to_char(hiredate,'yyyy')
 order by year;