MySQL获取学生各科前三的成绩

最新推荐文章于 2024-07-27 02:04:34 发布

情绪员Tim

最新推荐文章于 2024-07-27 02:04:34 发布

阅读量3.3k

点赞数 4

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/QQ675396947/article/details/121462264

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

创建科目成绩表：

DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores` (
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `subject` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `scores` VALUES ('学生a', 'java', '100');
INSERT INTO `scores` VALUES ('学生b', 'java', '90');
INSERT INTO `scores` VALUES ('学生c', 'java', '90');
INSERT INTO `scores` VALUES ('学生d', 'java', '60');
INSERT INTO `scores` VALUES ('学生e', 'java', '80');
INSERT INTO `scores` VALUES ('学生a', 'python', '100');
INSERT INTO `scores` VALUES ('学生b', 'python', '90');
INSERT INTO `scores` VALUES ('学生c', 'python', '90');
INSERT INTO `scores` VALUES ('学生d', 'python', '60');
INSERT INTO `scores` VALUES ('学生e', 'python', '80');

查询前三各科成绩：

SELECT s1.name,s1.subject,s1.score FROM scores s1 
WHERE(SELECT count(1)FROM scores s2 
WHERE s1.subject = s2.subject AND s2.score >= s1.score) <= 3 
ORDER BY s1.subject, s1.score DESC