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

 

 

 

 

 

 

  • 0
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值