笔试题: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语句及查询结果如下:
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
)
结果如下:
上一篇: asp.net登录注册页面
下一篇: SQL 必知必会--函数篇