Выберите лучший результат пользователя для каждого урока из одной таблицы на основе идентификаторов уроков в другой таблице

У меня есть сайт, на котором есть несколько курсов, и в каждом курсе есть несколько уроков. Я хотел бы выбрать наивысший балл за УРОК для заданных user_id и Course_id, упорядоченных по уроку_order.

TABLE 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


TABLE 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

sql mysql

26.07.2024 06:24

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

В MySQL 8+ мы можем использовать ROW_NUMBER() здесь:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, lesson_id
                                 ORDER BY total_score DESC) rn
    FROM scores
)

SELECT id, lesson_id, total_score, user_id
FROM cte
WHERE rn = 1
ORDER BY user_id, lesson_id;

26.07.2024 06:33

Ответ принят как подходящий

С помощью обычного запроса:

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

Результат:

lesson_id|course_id|lesson_order|user_id|max_score|
---------+---------+------------+-------+---------+
        1|        1|           1|      1|       80|
        2|        1|           2|      1|       50|
        3|        1|           3|      1|       95|
        4|        1|           4|      1|       80|

Это работает именно так, как нужно. Я понятия не имел, что это так просто. Спасибо!

— 26.07.2024 14:11

26.07.2024 09:38