需求:在MySQL5.7环境下,查询下面表中,各个学科前两名的学生的成绩;
1.准备数据
窗机表以及向表中插入数据
创建一张表:
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`id` int(11) DEFAULT NULL,
`kemu` varchar(255) DEFAULT NULL,
`score` int(255) DEFAULT NULL,
`no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
向表中插入数据:
INSERT INTO `grade` VALUES ('1001', '语文', '85', '1'); INSERT INTO `grade` VALUES ('1001', '数学', '86', '2'); INSERT INTO `grade` VALUES ('1002', '英语', '98', '3'); INSERT INTO `grade` VALUES ('1002', '语文', '94', '4'); INSERT INTO `grade` VALUES ('1002', '数学', '98', '5'); INSERT INTO `grade` VALUES ('1003', '数学', '56', '6'); INSERT INTO `grade` VALUES ('1003', '语文', '69', '7'); INSERT INTO `grade` VALUES ('1003', '英语', '68', '8'); INSERT INTO `grade` VALUES ('1004', '英语', '99', '9'); INSERT INTO `grade` VALUES ('1004', '数学', '97', '10');
2.MySQL编写语句
SELECT * from ( SELECT g.id,g.kemu,g.score,count(*) as rank from grade g LEFT JOIN grade g1 on g.kemu=g1.kemu and g.score<=g1.score GROUP BY g.id,g.kemu,g.score ORDER BY g.id,g.kemu,g.score desc ) n where rank<=2 ORDER BY kemu,rank
通过函数方式实现可以参考下面的链接:
https://www.jianshu.com/p/32e8c40372b3
http://blog.51cto.com/mydbs/2159794?source=dra