oracle分组函数

5个常用分组函数

* MIN（最小值）、MAX（ 最大值）、SUM（合计）、AVG （平均值）、

  COUNT 返回表中记录总数,适用于任意数据类型

* 组函数中 DISTINCT 消除重复行

* 组函数中空值处理

* 通过 GROUP BY 子句进行分组汇总

* having 子句

* select 语句的执行顺序

* 组函数的嵌套

 

1. MIN函数和MAX函数

5.1.1  
--查询入职日期最早和最晚的日期
SELECT MIN(HIREDATE),MAX(HIREDATE)
  FROM EMP;
--任何数据类型
SELECT * FROM EMP;

2. SUM函数和AVG函数

--查询职位以SALES开头的所有员工平均工资，最低工资，最高工资，工资和。
SELECT AVG(SAL),MIN(SAL),MAX(SAL),SUM(SAL)
  FROM EMP
 WHERE JOB LIKE 'SALES%'

3. COUNT函数

  count（*）返回满足条件的所有行数记录，包括null

  count（列 | 表达式）返回满足条件的所有行数记录，不包括null

--查询部门30有多少个员工
SELECT COUNT(*)
  FROM EMP
 WHERE EMP.DEPTNO=30
--COUNT(*)包括NULL在内,其他的分组不包括null
--一般情况下，用于统计员工的人数，count里面写主键值

--查询部门30有多个员工领取奖金
SELECT COUNT(COMM)
  FROM EMP
 WHERE EMP.DEPTNO=30
--COUNT(COMM)不包括NULL在内

4. 组函数中DISTINCT 消除重复记录后再使用函数组

查询所有员工的部门数量
SELECT COUNT (DISTINCT DEPTNO)  --distinct消除
  FROM EMP;

5. 空值处理  NVL

查询员工的平均奖金（有奖金的员工）
SELECT AVG(COMM) --550 不包括null在内（300+500+1400+0）/4
  FROM EMP;

在数组函数中使用NVL函数
--查询所有员工的平均奖金
SELECT AVG(NVL(COMM,0))--157 （300+500+1400+0）/14
  FROM EMP;

6. GROUP BY

  将表中满足where条件的记录按照指定的列划分成若干个小组

  group by 指定要分组的列

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

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

5.2.1  用GROUP　BY子句创建数据组
--查询每个部门的编号，平均工资
SELECT E.DEPTNO,AVG(E.SAL) PJ
  FROM EMP E
 GROUP BY E.DEPTNO
--select后面如果有分组函数，普通的列，那么普通的列一定出现在group by后面
--在GROUP BU 后面出现的列，不一定必须出现在select后面

5.2.2  使用GROUP　BY子句
--查询每个部门的平均工资
SELECT AVG(SAL) PJ FROM EMP  --查询的是emp表的平均工资
SELECT AVG(SAL) PJ FROM EMP GROUP BY DEPTNO;--按照部门进行分组

5.2.3  按多列分组的GROUP　BY子句
--查询每个部门每个岗位的工资总和。
SELECT DEPTNO,JOB,SUM(SAL)
  FROM EMP
 GROUP BY DEPTNO,JOB
 ORDER BY 1;
5.2.4  使用组函数的非法查询
--查询所有部门的名称及人数
SELECT D.DNAME,COUNT(E.EMPNO) RS
  FROM EMP E,DEPT D
 WHERE E.DEPTNO(+)=D.DEPTNO
 GROUP BY D.DNAME

7. HAVING 使用组函数的非法的查询

  * 不能在where子句中限制组

  * 可以通过HAVING子句限制

使用HAVING子句
--查询每个部门最高工资大于2900的部门编号、最高工资
--1.你要查询的数据来自那几张表，要查什么
SELECT DEPTNO,MAX(SAL) M
  FROM EMP
 GROUP BY DEPTNO
HAVING MAX(SAL)>2900 --分组函数的条件限定

--查询职位名称不以’SALE’开头的每个职位的工资总和大于5000的职位，工资总和，要求按工资总和升序排序
SELECT JOB,SUM(SAL) S
  FROM EMP
 WHERE JOB NOT LIKE 'SALE%'
 GROUP BY JOB
HAVING SUM(SAL)>5000
 ORDER BY 2;

8. SELECT 语句执行顺序

1.先执行from
2.where  非分组条件限定 || 表和表的连接条件
3.group by  需要分组的列
4.having  分组条件限定 || 组函数筛选判断
5.select  显示数据（列、表达式、组函数）
6.order by  排序

--查询职位名称是'SALESMAN','MANAGER','CLERK'的每种职位的部门编号，职位名称，平均工资，要求平均工资大于1000，并按平均工资的降序排序
SELECT E.DEPTNO,E.JOB,AVG(SAL)
  FROM DEPT D,EMP E
 WHERE D.DEPTNO=E.DEPTNO
   AND E.JOB IN ('SALESMAN','MANAGER','CLERK')
 GROUP BY E.DEPTNO,E.JOB
HAVING AVG(SAL)>1000
 ORDER BY 3 DESC

--查询每个部门的部门编号、部门名称、部门人数
SELECT E.DEPTNO,D.DNAME,COUNT(E.DEPTNO)
  FROM DEPT D, EMP E
 WHERE D.DEPTNO=E.DEPTNO
 GROUP BY E.DEPTNO,D.DNAME

*--显示每个部门中工资等级的种类 
SELECT DISTINCT D.DEPTNO,COUNT(E.SAL)
  FROM DEPT D,SALGRADE S,EMP E
 WHERE D.DEPTNO=E.DEPTNO
   AND E.SAL BETWEEN S.LOSAL AND S.HISAL
 GROUP BY D.DEPTNO

 

9. 组函数嵌套

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

--显示部门平均工资的最大值。
--第一步：查询部门的平均工资
--2 
SELECT MAX(AVG(SAL)) PJ
  FROM EMP
 GROUP BY DEPTNO