成绩表格Grade
StudentId | Class | Score |
---|
1 | A | 30 |
2 | A | 60 |
3 | A | 75 |
4 | A | 31 |
5 | A | 25 |
6 | B | 95 |
7 | B | 33 |
8 | B | 57 |
9 | B | 87 |
10 | C | 22 |
11 | C | 17 |
12 | C | 85 |
12 | C | 90 |
14 | C | 66 |
查询每个班级成绩最高前两名
CREATE TABLE `Grade` (
`StudentId` int(11) NOT NULL AUTO_INCREMENT,
`Class` varchar(100) DEFAULT NULL,
`Score` int(11) DEFAULT NULL,
PRIMARY KEY (`StudentId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(1, 'A', 30);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(2, 'A', 60);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(3, 'A', 75);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(4, 'A', 31);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(5, 'A', 25);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(6, 'B', 95);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(7, 'B', 33);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(8, 'B', 57);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(9, 'B', 87);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(10, 'C', 22);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(11, 'C', 17);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(12, 'C', 85);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(13, 'C', 90);
INSERT INTO bdg_go_base.Grade (StudentId, Class, Score) VALUES(14, 'C', 66);
SELECT
Grade.*
FROM
Grade INNER JOIN
( SELECT
Class,
GROUP_CONCAT(StudentId ORDER BY Score DESC) gStudentId
FROM
Grade
GROUP BY Class
) gGrade
ON
Grade.Class = gGrade.Class
AND FIND_IN_SET(Grade.StudentId, gGrade.gStudentId) BETWEEN 1 AND 2
ORDER BY
Grade.Class, Grade.StudentId;
结果
StudentId | Class | Score |
---|
2 | A | 60 |
3 | A | 75 |
6 | B | 95 |
9 | B | 87 |
12 | C | 85 |
13 | C | 90 |
参考资料
https://stackoverflow.com/a/15585351