根据指定user_id和course_id查询各lesson最高得分并按lesson_order排序

运营一个包含多门课程的网站，每门课程下设有多个lesson。需求为：根据指定的user_id和course_id，查询每个lesson对应的最高得分，并按lesson_order字段排序。

表结构及数据 #

lesson表 #

id   course_id   lesson_order
---|-----------|--------------
1  |     1     |      1
2  |     1     |      2
3  |     1     |      3
4  |     1     |      4
5  |     1     |      5
6  |     2     |      1
7  |     2     |      2
8  |     2     |      3

scores表 #

id   lesson_id   total_score    user_id
----|-----------|--------------|---------
 1  |     1     |      50      |    1
 2  |     1     |      80      |    1
 3  |     1     |      70      |    1
 4  |     2     |      30      |    1
 5  |     2     |      50      |    1
 6  |     3     |      80      |    1
 7  |     3     |      60      |    1
 8  |     3     |      90      |    1
 9  |     3     |      95      |    1
10  |     4     |      50      |    1
11  |     4     |      60      |    1
12  |     4     |      80      |    1
13  |     1     |      90      |    2
14  |     2     |      80      |    2
15  |     2     |      90      |    2
16  |     2     |     100      |    2

尝试的SQL（未成功） #

SELECT MAX(s.total_score), s.level_id
FROM (l.id AS score_id, s.total_score, s.level_id, l.lesson_order FROM scores GROUP BY s.level_id) S
JOIN levels L on l.level_id = s.level_id
WHERE user_id = 1 AND course_id = 1
ORDER BY l.lesson_order

要实现需求，需要先按user_id和lesson_id分组找出每个课程的最高得分，再关联lesson表筛选指定course_id并排序。正确的SQL如下：

SELECT 
    l.id AS lesson_id,
    l.lesson_order,
    MAX(s.total_score) AS highest_score
FROM 
    lesson l
LEFT JOIN 
    scores s ON l.id = s.lesson_id AND s.user_id = 1
WHERE 
    l.course_id = 1
GROUP BY 
    l.id, l.lesson_order
ORDER BY 
    l.lesson_order;

逻辑说明 #

1. 关联表：用LEFT JOIN关联lesson和scores，确保即使某lesson没有用户得分，也能显示（此时最高得分为NULL）。
2. 筛选条件：在JOIN时指定user_id = 1，同时WHERE子句筛选course_id = 1的课程。
3. 分组聚合：按lesson的id和lesson_order分组，用MAX()计算每个lesson的最高得分。
4. 排序：最后按lesson_order排序，保证结果顺序符合课程章节顺序。

原SQL的问题 #

1. 表名与字段错误：原SQL中误用levels表（实际为lesson表），字段level_id应为lesson_id。
2. 子查询语法错误：子查询缺少SELECT关键字，且分组逻辑未结合user_id和lesson_id，导致无法获取每个lesson的单独最高分。
3. 关联条件错误：表关联的字段不匹配，导致数据关联逻辑混乱。

内容的提问来源于stack exchange，提问作者Your Worshipful Heart