MySQL分组查询知识总结

MySQL分组查询知识总结

一、基本语法

SELECT
	分组依据列, 分组函数()
FROM
	要操作的表
WHERE
	分组前筛选条件, 处理原表中存在的数据
GROUP BY
	分组依据列
HAVING
	分组后筛选条件, 处理分组后的结果集
ORDER BY
	排序依据列 ASC / DESC;

二、注意事项

1. 查询列表是分组函数和 GROUP BY 后出现的字段
2. 分组函数做条件一定放在 HAVING 语句后
3. 优先使用分组前筛选, 速度快
4. GROUP BY 子句支持多字段分组, 表达式或函数分组
5. 排序应放在整个语句的最后

三、示例代码

1. 基础使用

# 查询每个部门的平均工资
SELECT 
	AVG(salary)
FROM 
	employees;
# 分组后代码
SELECT
	department_id, 
	AVG(salary)
FROM
	employees
GROUP BY
	department_id;
	
	
# 查询每个工种的最高工资
SELECT
	job_id, 
	MAX(salary)
FROM
	employees
GROUP BY
	job_id;


# 查询每个位置上的部门个数降序排列
SELECT
	location_id, 
	COUNT(*) AS count
FROM
	departments
GROUP BY
	location_id
ORDER BY
	count DESC;

2. 添加筛选条件

/* 添加筛选条件 */

# 案例1: 查询邮箱中含有a字符的, 每个部门的平均工资
SELECT
	email, 
	department_id, 
	AVG(salary) AS avg
FROM
	employees
WHERE
	email LIKE '%a%'
GROUP BY
	department_id
ORDER BY
	avg DESC;
	
# 案例2: 查询有奖金的每个领导手下员工的最高工资
SELECT
	manager_id, 
	MAX(salary) AS max
FROM
	employees
WHERE
	commission_pct IS NOT NULL
GROUP BY
	manager_id
ORDER BY
	max DESC;

3. 添加复杂筛选条件

/* 添加复杂筛选条件 */

# 案例1: 查询哪个部门的员工个数 > 2
# 分两步: 先查询每个部门的员工个数, 再根据此结果筛选员工数 > ２
SELECT
	department_id, 
	COUNT(*) AS count
FROM
	employees
GROUP BY
	department_id
HAVING
	count > 2
ORDER BY
	count DESC;

# 注意: where条件是对原始表进行筛选, having条件是对查询后的表进行筛选操作


# 案例2: 查询每个工种有奖金的员工的最高工资 > 12000 的工种编号和最高工资
# 第一步: 查询每个工种有奖金的员工的最高工资
SELECT
	job_id, 
	MAX(salary) AS max
FROM
	employees
WHERE
	commission_pct IS NOT NULL
GROUP BY
	job_id
HAVING
	max > 12000
ORDER BY
	max;


# 案例3: 查询领导编号 > 102 的每个领导手下的最低工资 > 5000 的领导编号是哪个?, 以及其最低工资
SELECT
	manager_id, MIN(salary) AS min
FROM
	employees
WHERE
	manager_id > 102
GROUP BY
	manager_id
HAVING
	min > 5000;

4.按表达式或者函数分组

/* 按表达式或者函数分组 */
# 案例: 按员工姓名长度分组, 查询每一组的员工个数, 筛选员工个数 > 5 的有哪些
SELECT 
	COUNT(*) AS c, 
	LENGTH(last_name) AS len_name
FROM
	employees
GROUP BY
	len_name
HAVING
	c > 5
ORDER BY
	len_name ASC;

5. 按多个字段进行分组

/* 按多个字段进行分组 */

# 案例: 查询每个部门每个工种的员工的平均工资, 并排序
SELECT
	department_id, job_id, AVG(salary)
FROM
	employees
WHERE
	department_id IS NOT NULL
GROUP BY
	department_id, job_id
HAVING
	AVG(salary) > 10000
ORDER BY
	AVG(salary) DESC;

6. 综合案例

/* 案例测试 */

# 1. 查询各job_id的员工工资的最大值, 最小值, 平均值, 总和, 并按 job_id 升序
SELECT
	job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM
	employees
GROUP BY
	job_id
ORDER BY
	job_id ASC;


# 2. 查询员工最高工资和最低工资的差距 (DIFFERENCE)
SELECT
	(MAX(salary) - MIN(salary)) AS DIFFERENCE
FROM
	employees;


# 3. 查询各个管理者手下员工的最低工资, 其中最低工资不能低于6000, 没有管理者的员工不计算在内
SELECT
	manager_id, 
	MIN(salary)
FROM 
	employees
WHERE
	manager_id IS NOT NULL
GROUP BY
	manager_id
HAVING
	MIN(salary) >= 6000;


# 4. 查询所有部门的编号, 员工数量和工资平均值, 并按平均工资降序
SELECT
	department_id, 
	COUNT(*), 
	AVG(salary)
FROM
	employees
GROUP BY
	department_id
ORDER BY
	AVG(salary) DESC;


# 5. 选择具有各个 job_id 的员工人数
SELECT
	job_id, COUNT(*)
FROM
	employees
GROUP BY
	job_id;

---

注: 本文档查询使用的数据来源为尚硅谷