查询每门课程成绩前两名的成绩

关注

转载最新推荐文章于 2026-04-05 02:29:50 发布 2.3k 阅读 AI 写同款· GEO 优化 ›

-- 42、查询每门功成绩最好的前两名 SELECT a.* FROM (SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student st LEFT JOIN score sc ON sc.s_id=st.s_id INNER JOIN course c ON c.c_id=sc.c_id AND c.c_id="01" ORDER BY sc.s_score DESC LIMIT 0,2) a UNION ALL SELECT b.* FROM (SELECT st.s_id,st.s 阅读详情

DROP TABLE IF EXISTS `score`;
 
CREATE TABLE `score` (
  `s_id` varchar(20) NOT NULL DEFAULT '',
  `c_id` varchar(20) NOT NULL DEFAULT '',
  `s_score` int(3) DEFAULT NULL,
  PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `score` */
 
insert  into `score`(`s_id`,`c_id`,`s_score`) values ('01','01',80),('01','02',90),('01','03',99),('02','01',70),('02','02',60),('02','03',80),('03','01',80),('03','02',80),('03','03',80),('04','01',50),('04','02',30),('04','03',20),('05','01',76),('05','02',87),('06','01',31),('06','03',34),('07','02',89),('07','03',98);

SELECT s1.* FROM score s1 WHERE
(
SELECT COUNT(1) FROM score s2 WHERE
s1.c_id=s2.c_id AND s2.s_score>=s1.s_score
)<=2
ORDER BY s1.c_id,s1.s_score DESC;