MySQL之分组查询

一、语法规则

select  分组函数，列（要求出现在group by后面）
from 表
【where筛选条件】
group by  分组的列表
【order by 子句】
#注意：
#查询列表必须特殊，要求是分组函数和group by后面出现的字段

二、分组查询的特点

1.分组查询中的筛选条件分两类
a.分组前筛选，数据源是原始表，位置在group by子句的前面，使用where关键字
b.分组后筛选，数据源是分组后的结果集，位置在group by子句的后面，使用having关键字
2.分组函数做条件肯定是放在having子句中，能使用分组前筛选的，就优先考虑使用分组前筛选

三、实际案例

1.添加分组前的筛选

#案例1：查询每个工种(job_id)的最高工资
SELECT
	MAX(salary),
	job_id
FROM
	employees
GROUP BY
	job_id;

#案例2：查询每个位置(local_id)上的部门个数
SELECT
	COUNT(*),
	local_id
FROM
	employees
GROUP BY
	local_id;

#案例3：查询邮箱(email)中包含a字符的，每个部门(department_id)的平均工资
SELECT
	AVG(salary),
	department_id
FROM
	employees
WHERE
	email LIKE '%a%'
GROUP BY
	department_id;

#案例4：查询每个领导(manager_id)手下有奖金(commission_pct)的的员工的最高工资
SELECT
	MAX(salary),
	manager_id
FROM
	employees
WHERE
	commission_pct  IS NOT NULL
GROUP BY
	manager_id;

2.添加分组后的筛选

#案例1：查询哪个部门的员工个数大于2
#第一步：查询每个部门员工个数
SELECT
	COUNT(*),
	department_id
FROM
	employees
GROUP BY
	department_id;

#第二步：根据第一步的结果进行筛选，这里就用到了HAVING关键字
SELECT
	COUNT(*),
	department_id
FROM
	employees
GROUP BY
	department_id
HAVING
	COUNT(*) > 2;

#案例2：查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
#第一步：查询每个工种有奖金的员工的最高工资
SELECT
	MAX(salary),
	job_id
FROM
	employees
WHERE
	commission_pct IS NOT NULL
GROUP BY
	job_id；
#第二步：第一步的结果中找最大工资大于12000的
SELECT
	MAX(salary),
	job_id
FROM
	employees
WHERE
	commission_pct IS NOT NULL
GROUP BY
	job_id
HAVING
	MAX(salary)>12000;

#案例3：查询领导编号大于102的每个领导手下员工的最低工资大于5000的员工的领导编号是哪个，以及其最低工资
#第一步：查询每个领导手下的员工的固定最低工资
SELECT
	MIN(salary),
	manager_id
FROM
	employees
GROUP BY
	manager_id;

#第二步：查询第一步中领导编号大于102的
SELECT
	MIN(salary),
	manager_id
FROM
	employees
WHERE
	manager_id>102
GROUP BY
	manager_id;

#第三步：查询第二步结果中最低工资大于5000的
SELECT
	MIN(salary),
	manager_id
FROM
	employees
WHERE
	manager_id>102
GROUP BY
	manager_id
HAVING
	MIN(salary)>5000;

3.按表达式或函数分组

#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选出员工个数>5的有哪些
#第一步：查询每个长度的员工个数
SELECT
	COUNT(*),
	LENGTH(name)
FROM
	employees
GROUP BY
	LENGTH(name);

#第二步：筛选第一步结果员工个数大于5的
SELECT
	COUNT(*),
	LENGTH(name)
FROM
	employees
GROUP BY
	LENGTH(name)
HAVING
	COUNT(*);

4.按多个字段分组

#案例：查询每个部门每个工种的员工的平均工资
SELECT
	AVG(salary),
	department_id,
	job_id
FROM
	employees
GROUP BY
	department_id,
	job_id;

5.分组查询中添加排序

#案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的降序显示
SELECT
	AVG(salary),
	department_id
FROM
	employees
GROUP BY
	job_id,
	department_id
ORDER BY
	AVG(salary) DESC;

分组查询就介绍到这里，如果发现错误或产生疑问请直接提出