每日一练0001

场景描述

一家在线教育平台有两张表：students 和 courses。

• students 表记录了学生的基本信息和他们的注册时间。
• courses 表记录了学生报名的课程信息。

平台希望分析学生的学习情况，今天的任务是找出每个学生报名的最新课程名称。

---

表结构和示例数据

表1：students

| studentid | studentname | registration_date |
|------------|--------------|--------------------|
| 1 | Alice | 2023-01-15 |
| 2 | Bob | 2023-02-20 |
| 3 | Charlie | 2023-03-10 |

表2：courses

| courseid | studentid | coursename | enrollmentdate |
|-----------|------------|-------------------|-----------------|
| 101 | 1 | Python Basics | 2023-01-20 |
| 102 | 1 | Advanced Python | 2023-02-15 |
| 103 | 2 | Data Science 101 | 2023-02-25 |
| 104 | 3 | SQL for Beginners | 2023-03-15 |
| 105 | 3 | Advanced SQL | 2023-03-20 |

---

问题

请写一条SQL语句，查询每个学生报名的最新课程名称。

---

最优解

SELECT 
    s.student_id,
    s.student_name,
    c.course_name AS latest_course
FROM 
    students s
JOIN 
    (
        SELECT 
            student_id, 
            course_name, 
            ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY enrollment_date DESC) AS rn
        FROM 
            courses
    ) c
ON 
    s.student_id = c.student_id
WHERE 
    c.rn = 1;

---

解题思路

1. 子查询：在 courses 表中，使用 ROW_NUMBER() 窗口函数为每个学生的课程按报名时间降序排列，并标记每个学生的最新课程为 rn = 1。
2. 主查询：将子查询的结果与 students 表进行连接，筛选出每个学生的最新课程。
3. 最终结果：输出学生的 ID、姓名和最新课程名称。

---

验证结果

根据示例数据，执行上述SQL语句后，结果如下：

| studentid | studentname | latest_course |
|------------|--------------|--------------------|
| 1 | Alice | Advanced Python |
| 2 | Bob | Data Science 101 |
| 3 | Charlie | Advanced SQL |

---