您现在的位置是:首页 >其他 >mysql-单张表查询各科成绩前两名的记录网站首页其他

mysql-单张表查询各科成绩前两名的记录

沐月浅歌 2024-06-17 10:19:31
简介mysql-单张表查询各科成绩前两名的记录

表结构

CREATE TABLE `t_score` (
  `id` varchar(100) NOT NULL,
  `course` varchar(100) NOT NULL,
  `score` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`,`course`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据

INSERT INTO `t_score` VALUES ('0001', '001', '30');
INSERT INTO `t_score` VALUES ('0001', '002', '88');
INSERT INTO `t_score` VALUES ('0001', '003', '45');
INSERT INTO `t_score` VALUES ('0002', '001', '57');
INSERT INTO `t_score` VALUES ('0002', '003', '33');
INSERT INTO `t_score` VALUES ('0003', '002', '87');
INSERT INTO `t_score` VALUES ('0003', '003', '45');
INSERT INTO `t_score` VALUES ('0004', '001', '40');
INSERT INTO `t_score` VALUES ('0004', '003', '78');
INSERT INTO `t_score` VALUES ('0004', '004', '66');
INSERT INTO `t_score` VALUES ('0005', '001', '70');
INSERT INTO `t_score` VALUES ('0005', '002', '91');
INSERT INTO `t_score` VALUES ('0005', '003', '55');
INSERT INTO `t_score` VALUES ('0005', '004', '65');

1、查询各科成绩前两名的记录

SELECT s1.id '学号',s1.course '课程',s1.score '分数' FROM t_score s1  
WHERE EXISTS (
        SELECT COUNT(1) FROM t_score s2 where s1.course=s2.course AND s2.score>s1.score HAVING COUNT(1)<2
)
ORDER BY s1.course, s1.score DESC;

2、查询各科成绩前两名的记录
SELECT s1.id '学号',s1.course '课程',s1.score '分数' FROM t_score s1  
WHERE  (
        SELECT COUNT(1) FROM t_score s2 where s1.course=s2.course AND s2.score>s1.score
) <2
ORDER BY s1.course, s1.score DESC;

好多人说数据重复了,就不对了,怎么出来大于2条或者3条、4条等等。其实问题就出在这,你品,你细品。《查询各科成绩前两名的记录》,人家让查前两名的成绩,比如:课程号-003,成绩由高至低倒序排名有一个90分,三个85分,那么最终记录应该是四条才对,因为三个85分并列第二名,那就他们就都属于前两名。

第一条数据执行 替换为数据 是不是就是 math = math and 100 > 100 这个时候很显然 100>100是不成立的,所以 count(*)统计出来的就是0 

第二条数据执行 替换为数据 math = math and 100 > 98 此时条件成立可以得出 count(*) = 1 以此类推;其实b.subject = a.subject作用可以理解为分组但未聚合,b.score > a.score相当于给组内排序;此方法适应数据量不大的场景,否则速度会很低

参考:

mysql 分组内取前几条数据_mysql分组取每组前几条记录_性感电眼郭德纲的博客-CSDN博客

mysql-单张表中《查询各科成绩前两名的记录》_ll569421432的博客-CSDN博客

查询各个科目不及格、良、优的数量

select course '课程',
count(distinct case when score < 60 then id end) '不及格',
count(distinct case when score >= 60 && score<80 then id end) '良',
count(distinct case when score >= 80 then id end) '优'
from t_score  group by course

风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。