MySQL GROUP BY分组取字段最大值示例代码详情

在MySQL数据库的实际应用中，经常会遇到需要按照某一字段进行分组，并获取每组中另一个字段最大值的需求。下面就通过示例代码详细介绍这一操作的实现方法。

创建一个示例表。假设我们有一个存储学生考试成绩的表 student_scores，包含字段 id（学生ID，主键）、student_name（学生姓名）、subject（考试科目）和 score（考试成绩）。创建表的SQL语句如下：

CREATE TABLE student_scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50),
    subject VARCHAR(50),
    score INT
);

接着，插入一些测试数据：

INSERT INTO student_scores (student_name, subject, score) VALUES
('张三', '数学', 85),
('张三', '英语', 90),
('李四', '数学', 92),
('李四', '英语', 88);

现在，我们要获取每个学生的最高成绩。这就需要使用 GROUP BY 结合聚合函数来实现。

方法一：使用子查询。先通过子查询获取每个学生的最高成绩，然后再与原表进行连接查询。

SELECT s.student_name, sub.max_score
FROM (
    SELECT student_name, MAX(score) AS max_score
    FROM student_scores
    GROUP BY student_name
) AS sub
JOIN student_scores s ON s.student_name = sub.student_name AND s.score = sub.max_score;

在这个查询中，子查询部分 SELECT student_name, MAX(score) AS max_score FROM student_scores GROUP BY student_name 首先按照学生姓名分组，求出每个学生的最高成绩。然后主查询通过连接条件将子查询结果与原表连接起来，获取对应的学生姓名和最高成绩。

方法二：使用 ROW_NUMBER() 窗口函数。

WITH ranked_scores AS (
    SELECT 
        student_name, 
        score, 
        ROW_NUMBER() OVER (PARTITION BY student_name ORDER BY score DESC) AS rank
    FROM student_scores
)
SELECT student_name, score
FROM ranked_scores
WHERE rank = 1;

这里使用 WITH 子句创建了一个名为 ranked_scores 的临时结果集。在这个临时结果集中，通过 ROW_NUMBER() 窗口函数按照学生姓名分区，并按照成绩降序排列，给每个学生的成绩分配一个排名。最后在主查询中筛选出排名为1的记录，即每个学生的最高成绩。

通过以上示例代码，我们详细展示了在MySQL中实现GROUP BY分组取字段最大值的不同方法，开发者可根据实际情况选择合适的方案。