mysql查询课程的前三名

看了下网上的查询课程前三名的,真是五花八门,真是 I服了U还各种错,看来啥事还是得自己动手.

表结构

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `s_no` bigint(20) DEFAULT NULL,
  `c_no` bigint(20) DEFAULT NULL,
  `score` float(64,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4;

 测试数据

ps:库名自己删除

INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (1, 1025, 9000, 98);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (2, 1031, 9000, 97);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (3, 1071, 9000, 97);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (4, 1091, 9000, 96);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (5, 1401, 9000, 96);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (6, 1061, 9000, 87);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (7, 1054, 9000, 68);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (8, 1021, 9000, 67);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (9, 1001, 9000, 59);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (10, 1012, 8000, 94);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (11, 1001, 8000, 84);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (12, 1025, 8000, 78);
INSERT INTO `mytest`.`student`(`id`, `s_no`, `c_no`, `score`) VALUES (13, 1026, 8000, 77);

 

ps:数字3为前三名

1:不包含(不存在分数相同)并列排名

 

select * 
from student s
where 
3 >
(
	select count(1) 
	from
		student t 
	where s.c_no = t.c_no and t.score > s.score
)

2:包含并列排名(其实就是再分组一下)

select * 
from student s
where 
3 >
(
	select count(1) 
	from
		(
		select
		c_no,
		score,
		count(c_no)
		from
		student
		GROUP BY c_no,score
		order by score desc
		) t 
	where s.c_no = t.c_no and t.score > s.score
)