3421. 查找进步的学生

题目描述

表：Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| student_id  | int     |
| subject     | varchar |
| score       | int     |
| exam_date   | varchar |
+-------------+---------+
(student_id, subject, exam_date) 是这张表的主键。
每一行包含有关学生在特定考试日期特定科目成绩的信息。分数范围从 0 到 100（包括边界）。

编写一个解决方案来查找 进步的学生。如果同时满足以下两个条件，则该学生被认为是进步的：

在 同一科目 至少参加过两个不同日期的考试。
他们在该学科 最近的分数 比他们第一次该学科考试的分数更高。

返回结果表以 student_id，subject 升序排序。

结果格式如下所示。

示例：

输入：

Scores 表：

+------------+----------+-------+------------+
| student_id | subject  | score | exam_date  |
+------------+----------+-------+------------+
| 101        | Math     | 70    | 15-01-2023 |
| 101        | Math     | 85    | 15-02-2023 |
| 101        | Physics  | 65    | 15-01-2023 |
| 101        | Physics  | 60    | 15-02-2023 |
| 102        | Math     | 80    | 15-01-2023 |
| 102        | Math     | 85    | 15-02-2023 |
| 103        | Math     | 90    | 15-01-2023 |
| 104        | Physics  | 75    | 15-01-2023 |
| 104        | Physics  | 85    | 15-02-2023 |
+------------+----------+-------+------------+

输出：

+------------+----------+-------------+--------------+
| student_id | subject  | first_score | latest_score |
+------------+----------+-------------+--------------+
| 101        | Math     | 70          | 85           |
| 102        | Math     | 80          | 85           |
| 104        | Physics  | 75          | 85           |
+------------+----------+-------------+--------------+

解释：

学生 101 的数学：从 70 分进步到 85 分。
学生 101 的物理：没有进步（从 65 分退步到 60分）
学生 102 的数学：从 80 进步到 85 分。
学生 103 的数学：只有一次考试，不符合资格。
学生 104 的物理：从 75 分进步到 85 分。

结果表以 student_id，subject 升序排序。

解法

方法一：窗口函数 + 子连接 + 条件过滤

首先，我们使用窗口函数 ROW_NUMBER() 计算每个学生在每个科目中的考试日期的排名，分别计算出每个学生在每个科目中的第一次考试和最近一次考试的排名。

然后，我们使用子连接 JOIN 操作将第一次考试和最近一次考试的分数连接在一起，最后根据题目要求筛选出最近一次考试的分数比第一次考试的分数高的学生。

MySQL

WITH
    RankedScores AS (
        SELECT
            student_id,
            subject,
            score,
            exam_date,
            ROW_NUMBER() OVER (
                PARTITION BY student_id, subject
                ORDER BY exam_date ASC
            ) AS rn_first,
            ROW_NUMBER() OVER (
                PARTITION BY student_id, subject
                ORDER BY exam_date DESC
            ) AS rn_latest
        FROM Scores
    ),
    FirstAndLatestScores AS (
        SELECT
            f.student_id,
            f.subject,
            f.score AS first_score,
            l.score AS latest_score
        FROM
            RankedScores f
            JOIN RankedScores l ON f.student_id = l.student_id AND f.subject = l.subject
        WHERE f.rn_first = 1 AND l.rn_latest = 1
    )
SELECT
    *
FROM FirstAndLatestScores
WHERE latest_score > first_score
ORDER BY 1, 2;

3421. 查找进步的学生

题目描述

解法

方法一：窗口函数 + 子连接 + 条件过滤

评论