sql语句中的Group By 分组查询

本篇文章中图片中的运行结果，是我使用其他表实现的，但是我使用的表结构和举出的样例是相同的，只是具体的字段名不相同而已，大家只看思路，不用在意这些细节哈。

分组查询存在的意义，为什么会有分组，分组查询主要针对哪些问题

分组查询的主要目的是对数据进行分类汇总，以便从数据集中提取有意义的统计信息和分析结果。在 SQL 中，分组查询通常通过 GROUP BY 子句来实现，将数据按照某个字段（或字段组合）进行分组，然后对每个分组应用聚合函数（如 SUM()、AVG()、COUNT()、MAX()、MIN() 等）来计算统计值。

分组查询的意义

1.数据统计和汇总
：分组查询可以对大量数据进行统计分析，比如计算每个分类的总和、平均值、最大值、最小值等。它能帮助我们了解数据的整体趋势和特征。
2.数据分类分析
：通过分组，可以对数据进行分类处理。例如，将销售数据按年份分组以查看每年的销售总额，或者将学生成绩按班级分组以分析每个班级的平均分数。
3.数据过滤
：分组后可以使用 HAVING 子句对分组后的结果进行过滤，从而筛选出符合特定条件的分组。这与 WHERE 子句的作用类似，但 HAVING 是在数据分组后起作用的，而 WHERE 则是在分组前起作用。
4.提高查询效率
：对大规模数据集进行分组汇总，能够简化数据分析的复杂度，使结果更加易于理解和解释。

为什么会有分组

分组的需求源于以下场景：

• 数据分析通常不仅需要查看单个记录，还需要分析记录的集合。例如，要了解某个班级的总成绩，就需要对该班级的所有学生的成绩进行分组，然后求和。
• 分组可以实现数据的聚合，使得我们可以对数据集进行更高层次的概览。例如，将销售数据按季度、地区等维度分组，可以更方便地进行商业决策。

分组查询主要针对的问题

1.统计汇总问题
：如统计总数、求和、平均值、最小值、最大值等。

• 例子：统计每个部门的员工人数、每个产品的总销售额等。
  2.分类问题
  ：如根据某一字段对数据进行分组，然后计算每组的数据特征。
• 例子：按地区分类统计销售额、按班级统计学生的平均成绩等。
  3.数据筛选问题
  ：当需要根据聚合后的结果进行筛选时，可以通过分组后的 HAVING 子句来实现。
• 例子：找出销售额超过100万元的地区，或者找出至少有3门课程成绩大于90分的学生。
  分组查询是数据库中分析数据的重要工具，尤其在数据挖掘、统计分析、商业报告等领域中起着关键作用。

语法：

select 聚合函数，列（要求出现在group by的后面）
from 表
where 筛选条件
group by 分组的列表
order by 子句

注意：除了出现在group by后面的字段，如果要在select后查询其他字段，必须用聚合函数进行聚合

特点：分组查询中的筛选条件分为两类：
分组前筛选： 数据源是原始表，用where，放在group by前面，因为在分组前筛选
分组后筛选：数据源是分组后的结果集 ，用having，放在group by后面，因为在分组后进行筛选。

一、 聚集函数

Max（属性名），min（属性名），count（属性名）:用于数值、字符、日期
Sum（属性名），avg(属性名)：用于数值
聚集函数会忽略空值
属性名前可以使用关键词all、distinct，默认为all

二、 分组：

group by 属性名1，属性名2，……
当group by后有多个属性，按照属性从左到右的顺序进行分组
当select后的属性列表中出现聚集函数时，其他的属性要么出现在group by中要么出现在聚集函数参数中

Where中不能使用聚集函数
Having 条件表达式，用于对分组的筛选，必须跟在group by后不能单独使用（mysql可以），在分组之后执行
Where 条件表达式，用于对元组的筛选，在分组之前执行

假设有两个表，course 和 score，分别包含课程信息和成绩信息，并且我们还假设有学生信息表 student。具体字段如下：

• student 表字段：student_id（学生编号）、name（姓名）、class（班级）、gender（性别）、birth_date（出生日期）、college（学院）、age（年龄）
• course 表字段：course_id（课程编号）、course_name（课程名称）
• score 表字段：student_id（学生编号）、course_id（课程编号）、score（成绩）

1. 查询每个班学生入学成绩最高分和最低分的差距，列名为“最大分差”

SELECT class, MAX(score) - MIN(score) AS 最大分差
FROM score 
JOIN student ON score.student_id = student.student_id
GROUP BY class;

2. 查询至少选修了三门课程的学生编号

SELECT student_id
FROM score
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) >= 3;

解释：

运行结果：

3. 统计各个班级男生和女生的平均年龄及人数

SELECT class, gender, AVG(age) AS 平均年龄, COUNT(*) AS 人数
FROM student
GROUP BY class, gender;

4. 查询至少被两名学生选修的课程名称

SELECT course.course_name
FROM score
JOIN course ON score.course_id = course.course_id
GROUP BY score.course_id
HAVING COUNT(DISTINCT score.student_id) >= 2;

5. 查询有至少两门课的成绩在90以上的学生姓名

SELECT student.name
FROM score
JOIN student ON score.student_id = student.student_id
WHERE score.score > 90
GROUP BY score.student_id
HAVING COUNT(DISTINCT score.course_id) >= 2;

6. 查询计算机学院至少有两门课的成绩在80以上的女学生学号、姓名和出生日期及其期末成绩的最高分

SELECT student.student_id, student.name, student.birth_date, MAX(score.score) AS 期末最高分
FROM score
JOIN student ON score.student_id = student.student_id
WHERE student.college = '计算机学院' AND student.gender = '女' AND score.score > 80
GROUP BY student.student_id, student.name, student.birth_date
HAVING COUNT(DISTINCT score.course_id) >= 2;

运行结果：

这些 SQL 语句分别满足了各个需求，适用于 MySQL 或其他类似的关系型数据库。

如果student表中只有birth_date而没有age，该怎么实现第3道题目的代码呢？

如果 student 表中只有 birth_date 而没有 age 字段，那么可以根据 birth_date 计算年龄。我们可以使用当前日期减去出生日期来计算年龄。假设我们使用 MySQL，具体的 SQL 实现可以如下：

SELECT class, gender, AVG(YEAR(CURDATE()) - YEAR(birth_date)) AS 平均年龄, COUNT(*) AS 人数
FROM student
GROUP BY class, gender;

解释

1.YEAR(CURDATE()) - YEAR(birth_date)：计算当前年份与出生年份的差值，作为大致的年龄（忽略了精确的生日计算）。
2.AVG(YEAR(CURDATE()) - YEAR(birth_date))：计算每个分组（按班级和性别）的平均年龄。
3.COUNT(*)：统计每个分组中的人数。
4.GROUP BY class, gender：根据班级和性别对数据进行分组。
这种方法会返回每个班级中男生和女生的平均年龄及人数。