根据学生表, 查找各科总成绩 排名第2-3名的学生 相同分数则是相同名次

思路:
1.得到所有学生的总分
2.根据总分进行排名, 分组合并相同分数, 得到排名对应的分数
3.根据 排名对应分数,  查找对应的学生

建表sql

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `code` varchar(11) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb3;

 
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (1, '张三', 'zs-1111', '语文', 100);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (2, '张三', 'zs-1111', '数学', 100);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (3, '张三', 'zs-1111', '英语', 100);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (4, '李四', 'ls-2222', '语文', 100);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (5, '李四', 'ls-2222', '数学', 100);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (6, '李四', 'ls-2222', '英语', 100);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (7, '王五', 'ww-3333', '语文', 90);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (8, '王五', 'ww-3333', '数学', 90);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (9, '王五', 'ww-3333', '英语', 90);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (10, '赵六', 'zl-4444', '语文', 80);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (11, '赵六', 'zl-4444', '数学', 80);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (12, '赵六', 'zl-4444', '英语', 80);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (13, '田七', 'tq-5555', '英语', 70);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (14, '田七', 'tq-5555', '数学', 70);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (15, '田七', 'tq-5555', '英语', 70);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (16, '董八', 'db-6666', '英语', 60);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (17, '董八', 'db-6666', '数学', 60);
INSERT INTO `test`.`student`(`id`, `name`, `code`, `subject`, `score`) VALUES (18, '董八', 'db-6666', '英语', 60);

建表成功后

====================

学生各科总分数情况
查询语句: 
  SELECT `name`,`code`,sum(score) score FROM `student` GROUP BY `code`,`name`;

 

 ====================

方法一: 

利用1个额外变量 对排名进行编号,  拿到对应排名的分数  再去找到对应分数的学生

# 排名计数
SET @rank= 0;

# (因为合并重复分数, 导致name, code等信息会丢失, 所以只能通过score字段作为条件 过滤出 对应总分数的学生)
select * from (SELECT `name`,`code`,sum(score) score FROM `student` GROUP BY `code`,`name`) d
where  score in (
	#只取第2,3名的总分数
	select score from (
		#获得排名 rank
		select  (@rank:=@rank + 1)rank_a, b.score from (  #8.0版本 as后跟的别名 用变量名会报错, 所以用了rank_a
				# 总分数分组, 合并重复的分数
				select score from (
					# 获得学生总分数
					SELECT `name`,`code`,sum(score) score FROM `student` GROUP BY `code`,`name`
				) a
			group by score order by score desc #根据分数倒排序, 保证排名正确
		) b
	)c where rank_a in (2,3)
);

 

查询结果正确

=================

方法二(不需要变量): 
遇到limit在子查询中会报错, 对limit包一层 select 防止其报错

 
# (因为合并重复分数, 导致name, code等信息会丢失, 所以只能通过score字段作为条件 过滤出 对应总分数的学生)
select * from (SELECT `name`,`code`,sum(score) score FROM `student` GROUP BY `code`,`name`) d
where  score in (

	# 多包一层 解决 limit报错的问题
		select score from (			
				# 总分数分组, 合并重复的分数, 只取第2,3名的总分数
				select score from (
					# 获得学生总分数
					SELECT `name`,`code`,sum(score) score FROM `student` GROUP BY `code`,`name`
				) a
			group by score order by score desc  limit 1,2 # 偏移是从0开始
		)b

);
 

最后结果: 已达到预期的效果

更新于 2021-11-19 12:37:59