MySQL-group by having分组查询

作者：马育民 • • 阅读：10200

分组查询

如下面例子，根据性别分组，然后取出身高最高的记录

问题

查询不同部门的最高工资，如果是用人来分析查找，首先根据部门对数据进行分组，然后在每组中查找最高工资，如下图：

用sql语句进行查询，需要用到 分组查询，如下：

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

语法

select 分组字段1，分组字段2,多行函数1,多行函数2 
from 表名 group by 分组字段1，分组字段2 
order by
注意：

select 分组字段后面可以 有多个 多行函数，但 不能 有 单行函数

例子

  1. 查询各个部门的最高工资，各个部门的人数
    select deptno,max(sal),count(*) from emp group by deptno
  2. 查询各个职务的人数、最高工资
    select job,count(*) from emp group by job
  3. 查询不同部门、不同职务的人数
    select deptno,job,count(*) from emp group by deptno,job
  4. 查询各个部门的平均工资
    select trim(to_char(round(avg(sal),2),'L999,999,999.00')) as 平均工资,deptno from emp group by deptno

注意

select 后面的字段，必须与 group by 分组字段 一致

如果group by 后面没有的字段，那么也不要出现在 select后面，mysql能执行但不正确，oracle报错

SELECT  deptno,MAX(sal),ename     FROM  emp GROUP BY deptno

查询出的 ename 不正确

可以有多个分组字段

SELECT  deptno,job,MAX(sal)     FROM  emp GROUP BY deptno,job

分组条件查询

  1. 查询各个部门，职务不是“MANAGER”、“PRESIDENT”的平均工资
    select deptno,avg(sal) from emp where job not in ('MANAGER','PRESIDENT')  group by deptno
    注意： where 在 group by 之前起作用，先筛选，然后再分组

having

问题

查询各个部门，职务不是“MANAGER”、“PRESIDENT”的平均工资，而且平均工资大于1500的记录

答案：

select deptno,avg(sal) from emp where job not in ('MANAGER','PRESIDENT')  group by deptno having avg(sal)>1500

语法：

having是 分组后 的 数据筛选，允许使用 多行函数，必须和group by搭配使用。
在执行 having之前，已经执行完 多行函数

select 分组字段,多行函数 from 表名 group by 分组字段 having 多行函数 [比较运算符] 值

having与where的区别

  • where 在 group by 之前起作用，having在group by 之后起作用

    where是对字段进行筛选，having是对分组中的多行函数进行筛选

  • where后面 不能 有 多行函数，having后面可以有 多行函数

  • having 和group by搭配使用，where不需要

例子

  1. 查询不同部门、不同职务，且人数大于1的记录
    select deptno,job,count(*) from emp group by deptno,job having count(*)>1

问题

查询不同职务的平均工资，职务不包含“MANAGER”、“PRESIDENT”，一种用 where语句筛选，一种用 having语句筛选，并考虑 哪个效率高

  • where
    select job,avg(sal) from emp where job not in ('MANAGER','PRESIDENT') group by job
  • having
    select job,avg(sal) from emp group by job having job not in ('MANAGER','PRESIDENT')
  • where是 先 筛选 再 分组查询，having是 先 分组查询 再 筛选，所以是where效率高

总结

编写顺序

Select 列名,函数
From 表名
Where 条件
Group by 列名
Having 条件
Order by 列名

执行顺序

顺序：from -> where -> group by -> select -> having -> order by

练习

  1. 按部门统计，工资大于1200 的平均工资

  2. 统计各部门的平均工资，并且平均工资大于2000的记录

  3. 统计各部门下工资大于1200 的员工的平均工资，并且平均工资大于2000的

  4. 算出每个职位的员工数和最低工资

  5. 算出每个部门，每个职位的平均工资和平均奖金(平均值包括没有奖金) ，如果平均奖金大于300，显示“奖 金不错”，如果平均奖金100 到300，显示“奖金一般”，如果平均奖金小于100，显示“基本没有奖金”， 按部门编号降序，平均工资降序排列

  6. 列出员工表中每个部门的员工数，和部门编号

  7. 统计每个部门下，每个职位的平均奖金和总工资(也要算没奖金的人)

原文出处：/show_1EF4uxx7IdY5.html

吉ICP备15003043号