oracle简单查询之分组查询-原创手记-慕课网

分组函数
1.1、什么叫分组函数

? 聚合函数也叫分组函数，有的也叫集合函数，它的数据源一般来自多组数据，但返回的时候一般是一组数据，聚合函数对一组行中的某个列执行计算并返回单一的值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用,所以有的时候也把其称之为分组函数
2.1、分类
函数名称返回值(结果)类型说明
MAX(DISTINCT\ ALL 列) 数值对所有数值求和
COUNT([DISTINCT\ ALL] 列)\ COUNT(*); 数值求非空的记录、数据个数
MAX([DISTINCT\ ALL] 数值日期列); 数值求最大值
MIN([DISTINCT\ ALL] 数值日期列); 数值求最小值
AVG([DISTINCT\ ALL] 数值列); 数值求平均值
2.2、 SUM(求总和)
2.2.1、说明

ALL表示对所有值求和
DISTINCT表示只对不同值求和(相同值只取一次)

2.2.2、示例代码

计算雇员姓名为 'SMITH'和 'ALLEN' 两个人的基本薪资和

SELECT SUM(sal) 
FROM emp 
WHERE ename IN('SMITH','ALLEN');

2.3、 COUNT(统计行数)
2.3.1、说明

ALL对所有记录，数组做统计
DISTINCT只对不同值统计(相同值只取一次)

2.3.2、示例代码

显示emp表中的总条数据

SELECT COUNT(*) 
FROM emp

统计 emp 职位类型的个数。

SELECT COUNT(DISTINCT job) 
FROM emp;

统计 emp 职位为 SALESMAN 的雇员个数

SELECT COUNT(*) 
FROM emp 
WHERE job='SALESMAN';

统计 emp 中 有佣金的雇员的个数

SELECT COUNT(comm) 
FROM emp;

2.4、 MAX(求最大值)
2.4.1、说明

ALL表示对所有的值求最大值 默认值
DISTINCT表示对不同的值求最大值,相同的只取一次

2.4.2、示例代码

查询所有雇员中最高的薪资

SELECT　MAX(sal)
FROM emp;

显示所有工资不同的员工中工资最高的

SELECT MAX(DISTINCT SAL) 
FROM EMP;

2.5、 AVG(求平均值)
2.5.1、说明

ALL表示对所有的值求最大值 默认值
DISTINCT表示对不同的值求最大值,相同的只取一次

2.5.2、示例代码

求所有员工工资的平均值

SELECT  (sal) 
FROM  emp;

求不重复的员工工资的平均值

SELECT  AVG(DISTINCT sal) 
FROM  emp;

2.6、MIN(求最小值)
2.6.1、说明

ALL表示对所有的值求最大值 默认值
DISTINCT表示对不同的值求最大值,相同的只取一次

2.6.2、示例代码

员工编号最小值

SELECT min(empno) 
FROM emp

查询工资最低的

SELECT MIN(sal)
FROM emp

查询年薪最低的

SELECT * ,MIN((NVL2(comm,comm+sal,comm)*12 )
FROM emp

2.7 、注意事项

分组函数只能出现在选择列表、having、order by子句中(不能出现在where前面)
当使用分组函数时，除了函数count(*)外，其他分组函数都会忽略NULL行。

2.8、单行函数和聚合函数的区别

单行函数操作时，根据函数的功能同时处理一行数据，返回每一行的处理结果；
聚合函数同时对分组后的一组行进行操作，返回分组后各组的处理结果

分组统计查询

1.1、概要

? Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。如果在查询的过程中需要按某一列的值进行分组,以统计该组内数据的信息时,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句group by子句一定要与分组函数结合使用,否则没有意义。
1.2、语法格式

SELECT [DISTINCT] * | 列名称 [别名] , 列名称 [别名] ,... | 统计函数 4、确定查询列

FROM 数据表 [别名] , 数据表 [别名] ,... 1、数据来源

[WHERE 条件(s)] 2、过滤数据行

[GROUP BY 分组字段, 分组字段, ...] 3、执行分组操作

[ORDER BY 字段 [ASC | DESC] , 字段 [ASC | DESC] ,...] 5、数据排序

1.3、示例代码

查询每个部门的人数

SELECT deptno ,COUNT(*)
FROM emp
GROUP BY deptno;

显示每个部门员工的平均工资

SELECT deptno ,AVG(sal) 平均工资 
FROM emp
GROUP BY deptno;

显示各个部门员工的工资+奖金

SELECT deptno,SUM(sal + NVL(comm,0))
FROM emp
GROUP BY deptno;

按照部门编号分组，求出每个部门的人数，平均工资(要求截取2位)(配合单行函数使用)

SELECT deptno, COUNT(empno), ROUND(AVG(sal),2) 
FROM emp
GROUP BY deptno;

按照职位分组，求出每个职位的最高和最低工资(单字段分组)

SELECT job, MAX(sal), MIN(sal)
FROM emp
GROUP BY job;

查询每个部门的每种岗位的平均工资和最低工资

SELECT AVG(sal), MIN(sal)
FROM emp
GROUP BY deptno;

先统计出各个职位(job)的平均工资(AVG),再统计平均工资最高的工资(分组函数嵌套)

SELECT MAX(AVG(sal))
FROM emp
GROUP BY job
注意:分组函数允许嵌套，但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段

查询每个岗位的总工资但不包括'SALESMAN'岗位(配合Where使用)

SELECT 
FROM emp
WHERE name !='SALESMAN'

按部门、不同的职位，统计员工的工资总额 (多字段统计)

SELECT deptno, job, sum(sal)
FROM emp   
GROUP BY deptno, job;

查询各个部门中相同职位的员工人数并且按部门编号排序(多字段统计排序)

SELECT DEPTNO, JOB,COUNT(*) 
FROM  emp
GROUP BY deptno,job  
ORDER BY deptno;

查询出每个部门的名称、部门的人数、平均工资(多表单字段查询)

1、确定表
    dept表
    emp表
2、确定关联字段
    deptno
3. 查询
  SELECT dname,count(e.empno),AVG(sal)
  FROM emp e,dept d
  WHERE  e.deptno(+) = d.deptno
  group by dname;

1.4、注意事项

GROUP BY后不可以接别名

SELECT  deptno dn ,AVG(sal) FROM emp  GROUP BY dn;

GROUP BY 后不能接数字

SELECT  deptno dn ,AVG(sal)
FROM emp  
GROUP BY 1;

GROUP BY 后不可以接select后没有的列名

SELECT  deptno dn ,AVG(sal) 
FROM emp  
GROUP BY job;

如果一个SELECT中使用了分组函数，任何不在分组函数中的列(表达式)必须要在GROUP BY中

SELECT  job ,deptno dn ,AVG(sal) 
FROM emp  
GROUP BY job;

group by之前可以使用where过滤数据,因为where是在分组之前起作用的，

1.5、使用HAVING过滤分组
1.5.1 说明

首先对数据行进行分组。
把所得到的分组应用到分组函数中。
最后显示满足having条件的记录

1.5.2 语法格式

SELECT [DISTINCT] * | 列名称 [别名] , 列名称 [别名] ,... | 统计函数 4、确定查询列

FROM 数据表 [别名] , 数据表 [别名] ,... 1、数据来源

[WHERE 条件(s)] 2、过滤数据行

[GROUP BY 分组字段, 分组字段, ...] [HIAVING 过滤分组] 3、执行分组操作

[ORDER BY 字段 [ASC | DESC] , 字段 [ASC | DESC] ,...] 5、数据排序

1.5.2 示例代码

查询部门的员工人数大于五部门编号

SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno 
HAVING COUNT(*)> 5;

查询部门工资总和大于10000的部门编号

SELECT deptno, SUM(sal) 
FROM emp
GROUP BY deptno 
HAVING SUM(sal)>10000;

查询平均工资低于2000的部门号和它的平均工资

SELECT deptno,AVG(sal) a
FROM emp
GROUP BY deptno 
HAVING avg(sal)>2000;

查询每个岗位的总工资并且不包括职位是'SALESMAN'岗位而且工资和大于5000

SELECT SUM(sal)
FROM emp
WHERE job!='SALESMAN'
GROUP BY job  HAVING SUM(sal)>5000

1.6、综合示例

查询非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列

1、查询出所有的非销售人员的信息

SELECT * FROM emp WHERE job!=SALESMAN';

2、按照职位进行分组，并且使用SUM函数统计

SELECT job,SUM(sal)
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job;

3、月工资的合计是通过统计函数查询的，所以现在这个对分组后的过滤要使用HAVING子句完成

SELECT job,SUM(sal)
FROM emp
WHERE job!='SALESMAN'
GROUP BY job
HAVING SUM(sal)>5000;

3、按照升序排列

SELECT job,SUM(sal) sum
FROM emp
WHERE job!='SALESMAN'
GROUP BY job
HAVING SUM(sal)>5000
ORDER BY sum ASC;

显示部门编号不是30的，的部门详细信息（部门编号、部门名称、部门人数、部门月薪资总和），并要求 部门月工资总和大于8000，输出结果按部门月薪资的总和降序排列。

SELECT d.deptno,d.dname,COUNT(*) 人数,NVL(SUM(e.sal),0) 月总收入
FROM dept d,emp e
WHERE d.deptno=e.deptno(+) AND d.deptno!=30
GROUP BY d.deptno,d.dname
HAVING SUM(e.sal) >8000
ORDER BY SUM(e.sal) DESC;

1.7、性能问题

? 能在where能过滤数据不要在having里过滤，A和B都能达到同样的目的，但是A性能相对好一些，因为A现将deptno=30的数据筛选出来，然后在将筛选的数据放入到临时表空间内进行分组；而B将全部的数据都读到临时表空间内，然后在临时表空间进行筛选数据，这样一来B就需要更大的临时表空间进行分组筛选，索引性能较差；