5-分组查询.md

什么是分组查询

一定要先分组，才能使用分组查询。在默认情况下一张表为一张表

select

…

from

…

where

….

group by

..

order by

…

二、sql 的执行过程（顺序）

1.from

2.where

3.group by （没有的时候默认为一张表）

​ ….

4.having

​ …..

5.select

​ …..

6.order by

​ ….

从 from 指定的表中查，经过 where 条件进行过滤出有价值的价值，过滤后进行 group by分组，分完组继续 使用 having 进行 过滤 ，然后 select 查询出来，最后进行排序。

三、sql 语句要注意的点

​ 分组函数不能直接使用在 where 后面。

一个思考题

1. 为什么这个语句会报错 /

   select sal form emp where sal>min(sal);

   ​ Q; min 是分组命令，要在group by执行完后才能执行。 如果写在where 中，where 执行时，group by 还没有执行。

2. 为什么这个没有分组 ，sum 函数可以使用呢？

   select sum(sal) from emp;

   Q: 因为 select 在 group by 之后执行。

执行流程分析

1.

统计不同岗位的工资和。----> 俺岗位分组
select job,sum(sal) from emp group by job;
mysql> select job,sum(sal) from emp group by job;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)
1. 先从 emp 表中查询数据 (from 和 where)
2. 根据 job 进行分组
3. 对每一组的数据进行 sum(sal)

select 字段要求
在一条 select 语句当中，如果有 group by 语句的话，select 后面只能跟： 参加分组的字段（job），以及分组函数(sum(sal))，其他的一律不能跟。

select ename(x错误！),job,sum(sal) from emp group by job;
--->Oracle会报错

案例分析

1. 求每个部门中的最高薪资

   1. 先根据部门进行分组 ----> 2.找出每一组中的最大值
   mysql> select max(sal),deptno from emp group by deptno;
   +----------+--------+
   | max(sal) | deptno |
   +----------+--------+
   |  3000.00 |     20 |
   |  2850.00 |     30 |
   |  5000.00 |     10 |
   +----------+--------+
   3 rows in set (0.01 sec)

2. 找出“每个部门，不同工作岗位”的最高薪资

   mysql> select job,deptno,max(sal) from emp group by job,deptno;
   +-----------+--------+----------+
   | job       | deptno | max(sal) |
   +-----------+--------+----------+
   | CLERK     |     20 |  1100.00 |
   | SALESMAN  |     30 |  1600.00 |
   | MANAGER   |     20 |  2975.00 |
   | MANAGER   |     30 |  2850.00 |
   | MANAGER   |     10 |  2450.00 |
   | ANALYST   |     20 |  3000.00 |
   | PRESIDENT |     10 |  5000.00 |
   | CLERK     |     30 |   950.00 |
   | CLERK     |     10 |  1300.00 |
   +-----------+--------+----------+
   9 rows in set (0.00 sec)

   技巧：两个字段联合成 1 个字段看（两个字段联合分组）

3. 找出每个部门最高薪资，要求显示最高薪资大于3000的

   思路一、
   1.找出每个部门最高薪资
      按照部门编号，进行分组，然后找出最高薪资
   2. 要求显示薪资大于3000 ，
     mysql>
     select 
     deptno,max(sal) 
     from 
     emp
     group by 
     deptno 
     having 
     max(sal)>3000 ;
   +--------+----------+
   | deptno | max(sal) |
   +--------+----------+
   |     10 |  5000.00 |
   +--------+----------+
   1 row in set (0.01 sec)
   
   思路二、
   先将 sal>3000 的都找出来，然后再进行分组
   select 
   deptno,max(sal)
   from 
   emp
   where
   sal>3000
   group by
   deptno;

having 不能单独使用，having可以对数据进行进一步过滤.

优化策略：
where和having，优先选择 where ，where 实在完成不了的，再选择having

4. 找出每个部门的平均薪资，只显示平均薪资大于2500的

   mysql> select deptno,avg(sal) from emp group by deptno ;
   +--------+-------------+
   | deptno | avg(sal)    |
   +--------+-------------+
   |     20 | 2175.000000 |
   |     30 | 1566.666667 |
   |     10 | 2916.666667 |
   +--------+-------------+
   3 rows in set (0.00 sec)
   
   # 分组函数中无法 在where中使用，所以此处不能使用where代替
   mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;
   +--------+-------------+
   | deptno | avg(sal)    |
   +--------+-------------+
   |     10 | 2916.666667 |
   +--------+-------------+
   1 row in set (0.00 sec)

5. 找出每个岗位的平均薪资，要求显示平均薪资大于 1500 的，除manager 岗位之外，要求按照平均薪资降序排

   select
   job,avg(sal)
   from
   emp
   where
   job<> 'manager'
   group by 
   job
   having 
   avg(sal)>1500
   order by
   avg(sal) desc; 
   
   先使用where 中选出了job不为 manager， 然后 使用having再进行过滤，最后 select job,avg(sal) 。
   
   mysql> select job,avg(sal) from emp where job<>'manager'group by job having avg(sal)>1500 order by avg(sal) desc;
   +-----------+-------------+
   | job       | avg(sal)    |
   +-----------+-------------+
   | PRESIDENT | 5000.000000 |
   | ANALYST   | 3000.000000 |
   +-----------+-------------+
   2 rows in set (0.00 sec)