欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

笔试题:SQL统计各班成绩第一名的学生信息

程序员文章站 2022-03-09 10:45:12
...

题目:统计各班成绩第一名的学生信息,数据信息如下图。

建表t_stu_score:

-- ----------------------------
-- Table structure for t_stu_score
-- ----------------------------
DROP TABLE IF EXISTS `t_stu_score`;
CREATE TABLE `t_stu_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(255) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  `score` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_stu_score
-- ----------------------------
INSERT INTO `t_stu_score` VALUES ('1', '路飞', '1', '90');
INSERT INTO `t_stu_score` VALUES ('2', '乔巴', '1', '85');
INSERT INTO `t_stu_score` VALUES ('3', '索隆', '1', '88');
INSERT INTO `t_stu_score` VALUES ('4', '鹰眼', '1', '96');
INSERT INTO `t_stu_score` VALUES ('5', '小新', '2', '66');
INSERT INTO `t_stu_score` VALUES ('6', '风间', '2', '92');
INSERT INTO `t_stu_score` VALUES ('7', '阿呆', '2', '81');
INSERT INTO `t_stu_score` VALUES ('8', '漩涡鸣人', '3', '88');
INSERT INTO `t_stu_score` VALUES ('9', '卡卡西', '3', '93');
INSERT INTO `t_stu_score` VALUES ('10', '我爱罗', '3', '86');
INSERT INTO `t_stu_score` VALUES ('11', '宇智波佐助', '3', '90');
INSERT INTO `t_stu_score` VALUES ('12', '大蛇丸', '3', '94');
INSERT INTO `t_stu_score` VALUES ('13', '宇智波鼬', '3', '94');

笔试题:SQL统计各班成绩第一名的学生信息


SQL语句及查询结果如下:

SELECT
	*
FROM
	t_stu_score a
WHERE
	a.score IN (
		SELECT
			MAX(score)
		FROM
			t_stu_score b
		WHERE
			a.class_id = b.class_id
		ORDER BY
			score DESC
	)

结果如下:

笔试题:SQL统计各班成绩第一名的学生信息 

 

相关标签: sql