需求:在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 ('', '语文', '', '');
INSERT INTO `grade` VALUES ('', '数学', '', '');
INSERT INTO `grade` VALUES ('', '英语', '', '');
INSERT INTO `grade` VALUES ('', '语文', '', '');
INSERT INTO `grade` VALUES ('', '数学', '', '');
INSERT INTO `grade` VALUES ('', '数学', '', '');
INSERT INTO `grade` VALUES ('', '语文', '', '');
INSERT INTO `grade` VALUES ('', '英语', '', '');
INSERT INTO `grade` VALUES ('', '英语', '', '');
INSERT INTO `grade` VALUES ('', '数学', '', '');
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