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