LeetCode-SQL-1112.每位学生的最高成绩

r囧r小猫 2022-08-28 13:48 179阅读 0赞

## **题目：** ##

编写一个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);

数据初始效果：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQWxsZW5MZXVuZ1g_size_6_color_FFFFFF_t_70_g_se_x_16][]

一句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;

查询结果：

![20210919110555697.png][]

##  ##

## **注意：** ##

如果不是使用MySQL，像Oracle，PostgreSQL的话，可以使用over partition by（分组后对组内数据排序），会更直观便捷。

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQWxsZW5MZXVuZ1g_size_6_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/fc84bb8f02a44ec7b9e2e12f747fc22f.png
[20210919110555697.png]: /images/20220828/dc2f01268e3d46d3b7b0d48c94b0528d.png