一 mysql处理方式
有一个学生分数表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
我想根据学生分组 ,得到每个分组中分数最高的那一条数据:
SELECT
t1.*
FROM
student t1
JOIN ( SELECT student_id, max( line ) AS line FROM student GROUP BY student_id ) AS t2 ON t1.student_id = t2.student_id
AND t1.line = t2.line
或者
SELECT
m.*,
MAX( m.line ) line_max
FROM
( SELECT * FROM student order by line desc limit 10000) m
GROUP BY
student_id
在mysql5.7版本之后,必须再排序之后加上limit关键字,20万所测试的数据是正确的,亲测验证
注意:
limit 是必须要加的,如果不加的话,数据不会先进行排序,通过 explain 查看执行计划,可以看到没有 limit 的时候,少了一个 DERIVED 操作。
二 oracle 处理方式
SELECT
*
FROM
( SELECT ROW_NUMBER () OVER ( PARTITION BY STUDENT_ID ORDER BY LINE DESC ) rn, a.* FROM STUDENT a )
WHERE
rn = 1
注意:
partition by是分区函数,partition by常同row_number() over一起使用。