看到别人的面经里有类似这样一个问题,我自己试了下,感觉做不出来。
DROP TABLE IF EXISTS `SC`;
CREATE TABLE `SC` (
`SId` varchar(10) DEFAULT NULL,
`CId` varchar(10) DEFAULT NULL,
`score` decimal(18,1) DEFAULT NULL
);
INSERT INTO `SC` VALUES ('01', '01', '80.0');
INSERT INTO `SC` VALUES ('01', '02', '90.0');
INSERT INTO `SC` VALUES ('01', '03', '99.0');
INSERT INTO `SC` VALUES ('02', '01', '70.0');
INSERT INTO `SC` VALUES ('02', '02', '60.0');
INSERT INTO `SC` VALUES ('02', '03', '80.0');
INSERT INTO `SC` VALUES ('03', '01', '80.0');
INSERT INTO `SC` VALUES ('03', '02', '80.0');
INSERT INTO `SC` VALUES ('03', '03', '80.0');
INSERT INTO `SC` VALUES ('04', '01', '50.0');
INSERT INTO `SC` VALUES ('04', '02', '30.0');
INSERT INTO `SC` VALUES ('04', '03', '20.0');
INSERT INTO `SC` VALUES ('05', '01', '76.0');
INSERT INTO `SC` VALUES ('05', '02', '87.0');
INSERT INTO `SC` VALUES ('06', '01', '31.0');
INSERT INTO `SC` VALUES ('06', '03', '34.0');
INSERT INTO `SC` VALUES ('07', '02', '89.0');
INSERT INTO `SC` VALUES ('07', '03', '98.0');
Sid是学生id,Cid是课程id。如果有重分,例如两个人100,那么考99的就是第3名。
看了网上的一些思路,写了下面的语句,基本就是判断某个学生在某门课程比他分数高的有几个,最后我们取0、1、2的结果。但是实际上没法得到0的结果,因为如果s2.score是最高分,那么s1.score > s2.score都不会成立,没有人比他更高,这样查询不到最高分的那个人。。
select s2.cid,s2.sid,s2.score,count(s1.sid) from SC s2
join SC s1
on s1.cid=s2.cid and s1.score > s2.score
group by s2.cid,s2.sid,s2.score having count(s1.sid) < 3
order by s2.cid, s2.score desc;