mysql分组后获取每个组排序后的第一条数据（整行）

最新推荐文章于 2025-04-21 19:53:53 发布

原创最新推荐文章于 2025-04-21 19:53:53 发布

· 7.9k 阅读

4 ·

版权

文章标签：

#sql #mysql

数据存储专栏收录该内容

3 篇文章

订阅专栏

这篇博客讨论了如何在MySQL中从学生分数表中，根据学生ID分组并获取每个分组内分数最高的记录。文章列举了错误的尝试，如只排序不分组、分组后再排序以及使用HAVING子句，指出这些方法的问题。最终，提供了在排序后添加LIMIT关键字的正确解决方案，并强调在MySQL5.7及以上版本中LIMIT的必要性，同时给出了验证方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

有一个学生分数表student，数据结构是这样的

id(当前表ID) student_id(学生ID) line（分数） subject_type(科目类型)

1 1 80 1

2 1 78 1

3 1 56 1

4 2 99 1

5 2 20 1

我想根据学生分组 ,得到每个分组中分数最高的那一条数据：

先来几种错误的写法：
（1）SELECT
    max.*, MAX(max.line) lineMax
FROM
    (SELECT * FROM student) max
GROUP BY
    student_id

这种写法你会发现line和lineMax的列中数据可能不一致

（2）SELECT
    max.*, MAX(max.line) lineMax
FROM
    (
        SELECT
            *
        FROM
            student
        ORDER BY
            line DESC
    ) max
GROUP BY
    student_id

你可能会说排序之后再分组就对了，抱歉，这种情况下对于某些小伙伴可能还是会有问题

（3）SELECT
    max.*, MAX(max.line) lineMax
FROM
    (
        SELECT
            *
        FROM
            student
        ORDER BY
            line DESC
    ) max
GROUP BY
    student_id
HAVING lineMax=line

这种情况下，表面上数据是对的，但是数据量大了之后你会发现有些数据丢失，其实还是错的

     正确写法：
SELECT
    max.*, MAX(max.line) lineMax
FROM
    (
        SELECT
            *
        FROM
            student
        ORDER BY
            line DESC
    LIMIT 100
    ) max
GROUP BY
    student_id

在mysql5.7版本之后，必须再排序之后加上limit关键字，20万所测试的数据是正确的，亲测验证

注意：
limit 是必须要加的，如果不加的话，数据不会先进行排序，通过 explain 查看执行计划，可以看到没有 limit 的时候，少了一个 DERIVED 操作。

可用以下SQL对每组数据进行验证

SELECT
    *
FROM
    student
WHERE
    student_id = 69
ORDER BY
    line DESC
LIMIT 100

注释：

不用非得limit，DISTINCT(主键)也可以

可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响并不明确。 防止合并的构造对于派生表和视图引用是相同的：
   1.聚合函数（ SUM() ， MIN() ， MAX() ， COUNT()等）
   2.DISTINCT
   3.GROUP BY
   4.HAVING
   5.LIMIT
   6.UNION或UNION ALL
   7.选择列表中的子查询
   8.分配给用户变量
   9.仅引用文字值（在这种情况下，没有基础表）