题目:
编写一个SQL查询,查询每位学生获得的最高成绩和对应的科目,若科目成绩并列,取course_id最小的一门。查询结果按student_id增序排序。
解决办法(基于MySQL):
创建enrollments表(弄得简单直观点,course_id改为course表示课程名称,student_id改为使用name表示学生姓名)
-- ----------------------------
-- Table structure for enrollments
-- ----------------------------
DROP TABLE IF EXISTS `enrollments`;
CREATE TABLE `enrollments` (
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`course` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`grade` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of enrollments
-- ----------------------------
INSERT INTO `enrollments` VALUES ('1张三', '1语文', 100);
INSERT INTO `enrollments` VALUES ('1张三', '2数学', 100);
INSERT INTO `enrollments` VALUES ('1张三', '3英语', 95);
INSERT INTO `enrollments` VALUES ('3王五', '4物理', 93);
INSERT INTO `enrollments` VALUES ('3王五', '3英语', 93);
INSERT INTO `enrollments` VALUES ('3王五', '2数学', 90);
INSERT INTO `enrollments` VALUES ('2李四', '1语文', 91);
INSERT INTO `enrollments` VALUES ('2李四', '2数学', 91);
INSERT INTO `enrollments` VALUES ('2李四', '4物理', 95);
INSERT INTO `enrollments` VALUES ('2李四', '3英语', 95);
数据初始效果:
一句SQL解决:
select name, min(course), grade from enrollments
where (name, grade) in (select name, max(grade) from enrollments group by name)
group by name, grade order by name;
查询结果:
注意:
如果不是使用MySQL,像Oracle,PostgreSQL的话,可以使用over partition by(分组后对组内数据排序),会更直观便捷。