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

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

建表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语句及查询结果如下：

SELECT*
FROMt_stu_score a
WHEREa.score IN (SELECTMAX(score)FROMt_stu_score bWHEREa.class_id = b.class_idORDER BYscore DESC)

结果如下：