一、概要

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

二、语法格式

----语句------------------------------------------------------------执行顺序-----
SELECT [DISTINCT] * | 列名称 [别名] , 列名称 [别名] ,... | 统计函数   4、确定查询列
FROM 数据表 [别名] , 数据表 [别名] ,...                              1、数据来源
[WHERE 条件(s)]                                                    2、过滤数据行
[GROUP BY 分组字段, 分组字段, ...]                                   3、执行分组操作
[ORDER BY 字段 [ASC | DESC] , 字段 [ASC | DESC] ,...]               5、数据排序

三、示例代码

1. 查询每个部门的人数

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

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

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

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

   SELECT deptno,SUM(sal + IFNULL(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 job;
   

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. 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是在分组之前起作用的，(执行顺序分析) ----废话

五、使用HAVING过滤分组

1、说明

1. 首先对数据行进行分组。
2. 把所得到的分组应用到分组函数中。
3. 最后显示满足having条件的记录
   作用：在分组之后再过滤掉不符合条件的分组

2、与where的区别

1. 只有having里面可以使用分组函数，where中不允许出现分组函数
2. 相同作用——都是根据条件过滤数据；不同的是where是在分组之前过滤数据，having是分组之后过滤分组数据。
3. 原则：能在where里过滤的数据就不要在having里面去过滤

3、语法格式

----语句-----------------------------------------------------------执行顺序---------
SELECT [DISTINCT] * | 列名称 [别名] , 列名称 [别名] ,... | 统计函数   5、确定查询列
FROM 数据表 [别名] , 数据表 [别名] ,...                              1、数据来源
[WHERE 条件(s)]                                                     2、过滤数据行
[GROUP BY 分组字段, 分组字段, ...]    [HIAVING 过滤分组]              3、执行分组操作
[HAVING 条件(s)]                                                    4、过滤分组数据
[ORDER BY 字段 [ASC | DESC] , 字段 [ASC | DESC] ,...]               6、数据排序

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

七、性能问题

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