【MySQL】十一、分组函数（group by，having），多字段分组查询

前言

group by：按照某个字段或者某些字段进行分组；
having：对分组之后的数组进行再次过滤；

1. group by

1.1 案例1：找出每个工作岗位的最高薪资。

select job,max(sal) from emp group by job;

这条sql语句的意思是，先对每个job进行分组，然后对每一组中求最大值。

执行顺序：先执行from，然后执行group by，最后select。

分组函数一般都会和group by联合使用！这也是为什么它被称为分组函数的原因。

任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的！

任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的！

任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的！

当一条sql语句没有group by，整张表的数据会自成一组。

下sql语句没有使用group by，但是存在一个缺省的group by。

select avg(sal) from emp;

查询结果：

+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |  1300.00 |
| MANAGER   |  2975.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1600.00 |
+-----------+----------+
5 rows in set (0.00 sec)

对于上案例，我们已经知道了每个工作岗位上的最高工资，但是我们想进一步知道最高工资对应的人名，似乎我们可以写成以下语句：

select ename, job, max(sal) from emp group by job;

查询结果：

+-------+-----------+----------+
| ename | job       | max(sal) |
+-------+-----------+----------+
| SCOTT | ANALYST   |  3000.00 |
| SMITH | CLERK     |  1300.00 |
| JONES | MANAGER   |  2975.00 |
| KING  | PRESIDENT |  5000.00 |
| ALLEN | SALESMAN  |  1600.00 |
+-------+-----------+----------+
5 rows in set (0.00 sec)

这条语句的执行结果显然是不正确的！ SMITH 的工资是800，这里成了1300。

这条语在MySQL中是可以执行的，但是在Oracle中是无法执行（语法错误，Oracle的语法比MySQL严谨）。即使MySQL中可以执行，但是结果毫无意义。

记住一个规则：

当一条语句中有group by ，select 后面只能跟分组函数和参与分组的字段。

1.2 案例2：找出每个工作岗位的平均薪资

select job,avg(sal) from emp group by job;

查询结果：

+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN  | 1400.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)

1.3 案例3：找出每个部门不同工作岗位的最高薪资（多字段分组查询）

我们先看下不同部门，不同岗位上员工工资信息；

select deptno,ename,job,sal from emp;

查询结果：

+--------+--------+-----------+---------+
| deptno | ename  | job       | sal     |
+--------+--------+-----------+---------+
|     10 | MILLER | CLERK     | 1300.00 |
|     10 | CLARK  | MANAGER   | 2450.00 |
|     10 | KING   | PRESIDENT | 5000.00 |

|     20 | SCOTT  | ANALYST   | 3000.00 |
|     20 | FORD   | ANALYST   | 3000.00 |
|     20 | SMITH  | CLERK     |  800.00 |
|     20 | ADAMS  | CLERK     | 1100.00 |
|     20 | JONES  | MANAGER   | 2975.00 |

|     30 | JAMES  | CLERK     |  950.00 |
|     30 | BLAKE  | MANAGER   | 2850.00 |
|     30 | ALLEN  | SALESMAN  | 1600.00 |
|     30 | WARD   | SALESMAN  | 1250.00 |
|     30 | MARTIN | SALESMAN  | 1250.00 |
|     30 | TURNER | SALESMAN  | 1500.00 |
+--------+--------+-----------+---------+
14 rows in set (0.00 sec)

select deptno, job, max(job) from emp group by deptno, job;

查询结果：

+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)

2. having 和where

2.1 案例1：找出每个部门的最高薪资，要求薪资大于2900的数据

第一步：找出每个每个部门的最高薪资；

select max(sal), deptno from emp group by deptno;

查询结果：

+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  5000.00 |     10 |
|  3000.00 |     20 |
|  2850.00 |     30 |
+----------+--------+
3 rows in set (0.00 sec)

第二步：找出薪资大于2900的；

select max(sal), deptno from emp group by deptno having max(sal) > 2900;

+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  5000.00 |     10 |
|  3000.00 |     20 |
+----------+--------+

这种方式可以查询到结果，但是这种方式的效果是极其低的！我们可以使用以下这种方式：

select max(sal), deptno from emp where sal > 2900 group by deptno;

先把薪资小于2900的信息过滤掉，然后再分组，这种执行效率就会更快一些！

因此，建议能够使用where过滤的尽量使用where。

2.2 案例2：找出每个部门的平均薪资，要求显示薪资大于2000的数据

select  avg(sal), deptno from emp group by deptno having avg(sal) > 2000;

查询结果：

+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2916.666667 |     10 |
| 2175.000000 |     20 |
+-------------+--------+
2 rows in set (0.00 sec)

这里只能使用having过滤，不能使用where关键字。以下写法是错误的，因为where后面是不能跟分组函数的。

select  avg(sal), deptno from emp  where avg(sal) > 2000 group by deptno; // 错误