分组统计查询

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、示例代码

  1. 查询每个部门的人数

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    SELECT 
FROM emp
WHERE name !='SALESMAN'

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

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

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

   SELECT DEPTNO, JOB,COUNT(*) 
   FROM  emp
   GROUP BY deptno,job  
   ORDER BY deptno;
  1. 查询出每个部门的名称、部门的人数、平均工资(多表单字段查询)
   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、注意事项

  1. GROUP BY后不可以接列的别名（根据执行顺序分析就知道了）

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

  2. GROUP BY 后不能接数字

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

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

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

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

    SELECT  job ,deptno dn ,AVG(sal) --deptno列group by 后面没有，使用会报错
FROM emp  
GROUP BY job;

    笔记：3和4总结为一句话

    1、在select中出现的列名必须在group by 中出现，否则，其他列名只能在分组函数中使用;而在group by 中出现的字段不一定要在select中出现

  5. group by之前可以使用where过滤数据,因为where是在分组之前起作用的，(执行顺序分析) ----废话

1.5、使用HAVING过滤分组

1.5.1 说明

  1. 首先对数据行进行分组。

  2. 把所得到的分组应用到分组函数中。

  3. 最后显示满足having条件的记录

    作用：在分组之后再过滤掉不符合条件的分组

1.5.2与where的区别

​ 1.只有having里面可以使用分组函数，where中不允许出现分组函数

​ 2.相同作用——都是根据条件过滤数据；不同的是where是在分组之前过滤数据，having是分组之后过滤分组数据。

​ 3.原则：能在where里过滤的数据就不要在having里面去过滤

1.5.3 语法格式

----语句-----------------------------------------------------------执行顺序---------

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

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

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

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

[HAVING 条件(s)]                                                    4、过滤分组数据

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

1.5.4 示例代码

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

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

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

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

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

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

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

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

1.6、综合示例

  1. 查询非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$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;

    4、按照升序排列

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

  2. 显示部门编号不是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;
或
select deptno,d.dname ,count(*) peonum,sum(e.sal) s
from dept d left join emp e using(deptno)  --注意:using()中的字段在使用时不能有前缀。
where deptno !=30
group by deptno ,d.dname
having sum(e.sal)>8000
order by s desc;

1.7、性能问题

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

results matching ""

    No results matching ""