sql 查询出平均成绩，根据平均成绩分数相同，排名相同

最新推荐文章于 2023-07-09 17:23:05 发布

不想做咸鱼的王富贵

最新推荐文章于 2023-07-09 17:23:05 发布

阅读量1.9k

点赞数 1

分类专栏： java基础

本文链接：https://blog.csdn.net/weixin_43564627/article/details/115146640

版权

java基础专栏收录该内容

70 篇文章 1 订阅

订阅专栏

本文讲述了面试中遇到的SQL问题，重点讲解了如何根据学生编号分组查询平均成绩，并利用SQL变量和排名算法解决相同平均分的并列排名。通过实例演示了如何使用@符号和:=运算符来实现这一过程，帮助读者避免面试中的常见陷阱。

摘要由CSDN通过智能技术生成

今天面试被sql绊倒了，废话少说，希望以后面试时不再填坑。需求基本和标题一样。

表table1

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1`  (
  `num` int(11) NULL DEFAULT NULL,
  `course` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `score` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of table1
-- ----------------------------
INSERT INTO `table1` VALUES (1, '语文', '70');
INSERT INTO `table1` VALUES (1, '数学', '80');
INSERT INTO `table1` VALUES (1, '英语', '90');
INSERT INTO `table1` VALUES (2, '语文', '80');
INSERT INTO `table1` VALUES (2, '数学', '80');
INSERT INTO `table1` VALUES (2, '英语', '77');
INSERT INTO `table1` VALUES (3, '语文', '78');
INSERT INTO `table1` VALUES (3, '数学 ', '93');
INSERT INTO `table1` VALUES (3, '英语', '91');
INSERT INTO `table1` VALUES (4, '语文', '75');
INSERT INTO `table1` VALUES (4, '数学', '89');
INSERT INTO `table1` VALUES (4, '英语', '76');

SET FOREIGN_KEY_CHECKS = 1;

首先根据学生 num 分组查询出来平均成绩

select t1.num, round(avg(t1.score)) as avg_score

from table1  t1
GROUP BY t1.num
ORDER BY avg(t1.score) desc

添加额外的列并排名平均分相同的并列同一名

SELECT t.*,
    @rows:=@rows+1 as rows,
		if(@gnum=avg_score,@rownum:=@rownum,@rownum:=@rownum+1) as rank,
		@gnum:=avg_score
FROM (
select t1.num, round(avg(t1.score)) as avg_score

from table1  t1
GROUP BY t1.num
ORDER BY avg(t1.score) desc
)t ,(select @rownum:=0,@gnum:=0,@rows:=0) number

结果如下
在这里插入图片描述
此时运用到了sql变量以及赋值
sql变量用@来表示，赋值用:=来实现；