All rights reserved.No part of this article may be reproduced or distributed by any means,or stored in a database or retrieval system,without the prior written permission of persistenceGoing author
https://blog.csdn.net/persistencegoing/article/details/84376427
有一个学生分数表student,数据结构是这样的
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`student_id` int(11) DEFAULT NULL,
`line` int(11) DEFAULT NULL,
`subject_type` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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
我想根据学生分组 ,得到每个分组中分数最高的那一条数据:
大约20万数据
先来几种错误的写法:
(1)
SELECT
t.*, MAX(t.line) lineMax
FROM
student t
GROUP BY
t.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
这种情况下,表面上数据是对的,但是数据量大了之后你会发现有些数据丢失,有几率查询不出来,其实还是错的
(4)
SELECT
max.*, MAX(max.line) lineMax
FROM
(
SELECT
*
FROM
student
ORDER BY
line DESC
LIMIT 100
) max
GROUP BY
student_id
这种情况下终于结果对了,多操作几次也对了,但是你会发现,你是先排序只取100条数据,然后再排序后的结果再分组,所以也是表面正确
(5) 正确写法:
SELECT
max.*, MAX(max.line) lineMax
FROM
(
SELECT DISTINCT
(a.id) tt,
a.*
FROM
student a
ORDER BY
a.line DESC
) max
GROUP BY
student_id
DISTINCT(主键)让数据集先进行排序再分组取排序后的第一条
各位可用以下SQL对每组数据进行验证
SELECT
*
FROM
student
WHERE
student_id = 69
ORDER BY
line DESC
LIMIT 100
博主强烈推荐:https://blog.csdn.net/persistencegoing/article/details/84376427
希望大家关注我一波,防止以后迷路,有需要的可以加群讨论互相学习java ,学习路线探讨,经验分享与java求职
群号:721 515 304