mysql菜鸟教程
7.2 数据分组：GROUP BY
在实际的数据分析中，仅仅计算整个表的统计值往往不够，我们需要按照某些维度对数据进行分组统计。比如：计算每个科目的平均分、统计每个学生的总成绩、按部门统计员工人数等。GROUP BY子句就是SQL中实现数据分组的核心工具，它能够将数据按照一个或多个列进行分组，然后对每个组分别应用聚合函数。
一、GROUP BY的核心价值
为什么需要分组？
-
多维分析：按照不同维度（如科目、学生、部门）进行统计
-
对比分析：比较各组之间的差异（如各科平均分对比）
-
汇总报表：生成分类汇总报表（如按季度销售统计）
-
数据洞察：发现数据中的模式和规律
典型应用场景
-
电商平台：统计每个类别的商品销售额
-
学校系统：计算每个班级的平均成绩
-
人力资源：按部门统计员工人数和平均工资
-
网站分析：按小时统计用户访问量
二、GROUP BY基础语法
GROUP BY子句通常与聚合函数一起使用，位于WHERE之后、ORDER BY之前：
SELECT 分组列1, 分组列2, ..., 聚合函数(列)
FROM 表名
[WHERE 条件] -- 分组前筛选
GROUP BY 分组列1, 分组列2, ...
[HAVING 分组后条件] -- 分组后筛选
[ORDER BY 排序];
执行顺序理解：
-
FROM：定位表
-
WHERE：筛选行（分组前过滤）
-
GROUP BY：将数据分组
-
聚合函数：对每个组进行计算
-
HAVING：筛选分组（分组后过滤）
-
SELECT：选择输出列
-
ORDER BY：排序结果
继续使用上一节的scores成绩表进行演示：
-- 表结构回顾
CREATE TABLE scores (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
student_name VARCHAR(50) NOT NULL,
subject VARCHAR(50) NOT NULL,
score DECIMAL(5,2),
exam_date DATE NOT NULL
);
-- 表中包含15条成绩记录，涉及5名学生和3个科目
三、基础分组示例
1. 单列分组：按科目统计
计算每个科目的考试次数、平均分、最高分和最低分：
SELECT
subject,
COUNT(*) AS exam_count,
COUNT(score) AS valid_score_count, -- 忽略NULL
ROUND(AVG(score), 2) AS avg_score,
MAX(score) AS max_score,
MIN(score) AS min_score
FROM scores
GROUP BY subject;
结果解释：
-
数据按照subject列的值（数学、英语、物理）被分成3组
-
每组分别计算COUNT、AVG等聚合函数
-
输出每个组一行结果
2. 按学生分组
计算每个学生的总成绩、平均分和考试次数：
SELECT
student_id,
student_name,
COUNT(*) AS exam_count,
COUNT(score) AS valid_count,
SUM(score) AS total_score,
ROUND(AVG(score), 2) AS avg_score
FROM scores
GROUP BY student_id, student_name; -- 按学号和姓名分组（实际按学号就够）
注意：这里按student_id和student_name分组是为了确保输出学生姓名，因为student_name依赖于student_id，在逻辑上可以只按student_id分组。
3.多列分组
当需要更细粒度的统计时，可以按多个列分组。例如，按学生和科目分组（即每个学生每门课只有一个成绩，所以每组只有一条记录，意义不大）。更有意义的例子：统计每个科目、每个考试日期的平均分。
-- 按科目和考试日期分组
SELECT
subject,
exam_date,
COUNT(*) AS exam_count,
ROUND(AVG(score), 2) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY subject, exam_date
ORDER BY subject, exam_date;
结果：每个科目在每个考试日期上的平均分（如果有多次考试）。
四、GROUP BY与聚合函数的配合
GROUP BY几乎总是和聚合函数一起使用，但也可以单独使用GROUP BY（去重效果，但不推荐）。聚合函数作用于每个组内的数据。
常见的聚合函数与GROUP BY组合
-
COUNT(*)：每组行数
-
COUNT(列)：每组非空值个数
-
SUM(列)：每组总和
-
AVG(列)：每组平均值
-
MAX(列) / MIN(列)：每组的极值
示例：统计每个学生缺考科目数
SELECT
student_id,
student_name,
COUNT(*) - COUNT(score) AS missing_count
FROM scores
GROUP BY student_id, student_name
HAVING missing_count > 0; -- 只显示有缺考的学生
五、HAVING子句：筛选分组
HAVING子句用于对分组后的结果进行筛选，类似于WHERE但作用在分组上。WHERE在分组前过滤行，HAVING在分组后过滤组。
语法示例
-- 找出平均分大于85的科目
SELECT
subject,
ROUND(AVG(score), 2) AS avg_score
FROM scores
GROUP BY subject
HAVING AVG(score) > 85;
-- 找出平均分大于80且至少有2个学生的科目
SELECT
subject,
ROUND(AVG(score), 2) AS avg_score,
COUNT(*) AS student_count
FROM scores
GROUP BY subject
HAVING AVG(score) > 80 AND COUNT(*) >= 2;
WHERE vs HAVING 对比
特性
WHERE
HAVING
作用时机
分组前过滤行
分组后过滤组
可否使用聚合函数
不能
可以（如HAVING AVG(score) > 80）
可否使用别名
通常不能（取决于数据库）
部分数据库支持别名
与GROUP BY关系
独立，可无GROUP BY
必须与GROUP BY一起使用
六、重要注意事项与常见错误
1. SELECT子句中的列必须满足的条件
当使用GROUP BY时，SELECT中的非聚合列必须出现在GROUP BY子句中（或者是函数依赖的列）。否则，结果可能不确定（在MySQL中可能随机返回一个值，但其他数据库会报错）。
错误示例：
-- 错误：student_name没有在GROUP BY中出现，也没有被聚合
SELECT student_id, student_name, AVG(score)
FROM scores
GROUP BY student_id; -- MySQL允许但结果不可靠
正确做法：
要么把student_name也加入GROUP BY，要么使用聚合函数（如MAX(student_name)）但意义不大。
2. NULL值的分组
GROUP BY会将所有NULL值视为同一组：
-- 假设scores表中某行score为NULL
SELECT score, COUNT(*)
FROM scores
GROUP BY score; -- NULL值会聚集在一组
3. 使用别名
在GROUP BY和HAVING中通常不能使用列别名（除非数据库支持），应该使用原始列名或表达式。
4. 性能优化
-
对GROUP BY的列建立索引可提高性能
-
尽量先用WHERE过滤掉不需要的行，减少分组数据量
-
避免对大文本列进行分组
七、高级用法与技巧
1. 按表达式分组
可以根据计算结果分组，如按分数段统计学生人数：
SELECT
CASE
WHEN score >= 90 THEN '优秀'
WHEN score >= 80 THEN '良好'
WHEN score >= 60 THEN '及格'
ELSE '不及格'
END AS score_level,
COUNT(*) AS student_count
FROM scores
WHERE score IS NOT NULL
GROUP BY score_level;
2. 使用GROUP_CONCAT()查看组内详情
GROUP_CONCAT()是一个非标准的但非常有用的函数，可以将组内某列的值连接成一个字符串：
-- 查看每个科目有哪些学生参加了考试
SELECT
subject,
GROUP_CONCAT(student_name ORDER BY student_name) AS students,
COUNT(*) AS student_count
FROM scores
GROUP BY subject;
3. ROLLUP 和 CUBE（了解）
MySQL支持WITH ROLLUP，可以在分组结果中增加一行总计或小计：
-- 按科目分组统计，并添加总计行
SELECT
IFNULL(subject, '总计') AS subject,
COUNT(*) AS exam_count,
ROUND(AVG(score), 2) AS avg_score
FROM scores
GROUP BY subject WITH ROLLUP;
八、实战练习与解答
练习题目
基于scores表，编写SQL实现以下需求：
-
基础分组：统计每个学生参加了几门考试（有效成绩数）
-
多列分组：统计每个科目在不同考试日期的平均分
-
HAVING筛选：找出平均分大于85分的学生
-
条件分组：统计每个学生数学和英语的平均分（分别显示）
-
综合应用：统计每个分数段（<60, 60-80, 80-90, >=90）的学生人数
-
挑战题：找出每门课成绩最高的学生（显示科目、学生姓名和成绩）
参考答案
-- 1. 统计每个学生参加了几门考试（有效成绩数）
SELECT
student_id,
student_name,
COUNT(score) AS exam_count
FROM scores
GROUP BY student_id, student_name
ORDER BY exam_count DESC;
-- 2. 统计每个科目在不同考试日期的平均分
SELECT
subject,
exam_date,
ROUND(AVG(score), 2) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY subject, exam_date
ORDER BY subject, exam_date;
-- 3. 找出平均分大于85分的学生
SELECT
student_id,
student_name,
ROUND(AVG(score), 2) AS avg_score
FROM scores
WHERE score IS NOT NULL
GROUP BY student_id, student_name
HAVING AVG(score) > 85;
-- 4. 统计每个学生数学和英语的平均分
SELECT
student_id,
student_name,
ROUND(AVG(CASE WHEN subject = '数学' THEN score END), 2) AS math_avg,
ROUND(AVG(CASE WHEN subject = '英语' THEN score END), 2) AS english_avg
FROM scores
GROUP BY student_id, student_name;
-- 5. 统计每个分数段的学生人数
SELECT
CASE
WHEN score >= 90 THEN '90分以上'
WHEN score >= 80 THEN '80-89分'
WHEN score >= 60 THEN '60-79分'
ELSE '60分以下'
END AS score_range,
COUNT(*) AS student_count
FROM scores
WHERE score IS NOT NULL
GROUP BY score_range
ORDER BY
CASE score_range
WHEN '90分以上' THEN 1
WHEN '80-89分' THEN 2
WHEN '60-79分' THEN 3
ELSE 4
END;
-- 6. 每门课成绩最高的学生
SELECT
s1.subject,
s1.student_name,
s1.score
FROM scores s1
INNER JOIN (
SELECT subject, MAX(score) AS max_score
FROM scores
GROUP BY subject
) s2 ON s1.subject = s2.subject AND s1.score = s2.max_score;
GROUP BY是进行数据汇总和分析的核心工具，它将数据从细节记录提升到更高层次的洞察。掌握它，你将能从数据中提取出更有价值的统计信息。
发表评论
所有评论