MySQL Выбрать из нескольких таблиц и самую последнюю запись
У меня проблемы с запросом выбора, и я не могу понять, как исправить. У меня две таблицы:
TABLE_students
|--------|------------|--------|
| STU_ID | EMAIL | NAME |
|--------|------------|--------|
| 1 | [email protected] | Bob |
| 2 | [email protected] | Joe |
| 3 | [email protected] | Tim |
--------------------------------
TABLE_scores
|--------|------------|-------------|--------|
| SRE_ID | STU_ID | DATE | SCORE |
|--------|------------|-------------|--------|
| 91 | 2 | 2018-04-03 | 78 |
| 92 | 2 | 2018-04-06 | 89 |
| 93 | 3 | 2018-04-03 | 67 |
| 94 | 3 | 2018-04-06 | 72 |
| 95 | 3 | 2018-04-07 | 81 |
----------------------------------------------
Я пытаюсь выбрать данные из обеих таблиц, но у меня есть несколько требований. Мне нужно выбрать ученика, даже если у него нет оценок в таблице оценок. Мне также нужны только последние результаты.
Приведенный ниже запрос возвращает только тех учащихся, которые имеют оценку, а также дублирует, возвращает в общей сложности 5 строк (поскольку имеется пять оценок). Я хочу, чтобы запрос возвращал три строки (по одной для каждого учащегося) и их последнее значение оценки (или NULL, если у них нет оценки):
SELECT students.NAME, scores.SCORE FROM TABLE_students as students, TABLE_scores AS scores WHERE students.STU_ID = scores.STU_ID;
Мне сложно понять, как вывести всех учеников, независимо от того, есть ли у них баллы, и как вывести только последний результат, если он у них есть.
Спасибо!
Ответы 2
Вы можете использовать коррелированный подзапрос:
SELECT *,
(SELECT score FROM TABLE_scores sc
WHERE sc.stu_id = s.stu_id ORDER BY DATE DESC LIMIT 1) AS score
FROM TABLE_students s
Это вариант вопроса наибольшее число n на группу, который часто встречается в Stack Overflow.
Я бы сделал это с помощью пары соединений:
SELECT s.NAME, c1.DATE, c1.SCORE
FROM students AS s
LEFT JOIN scores AS c1 ON c1.STU_ID = s.STU_ID
LEFT JOIN scores AS c2 ON c2.STU_ID = s.STU_ID
AND (c2.DATE > c1.DATE OR c2.DATE = c1.DATE AND c2.SRE_ID > c1.SRE_ID)
WHERE c2.STU_ID IS NULL;
Если c2.STU_ID имеет значение NULL, это означает, что LEFT JOIN не соответствует ни одной строке с более высокой датой (или большим SRE_ID в случае ничьей), чем строка в c1. Это означает, что строка в c1 должна быть самой последней, потому что более новой строки нет.
P.S .: Пожалуйста, изучите синтаксис JOIN и избегайте объединений в стиле запятых. JOIN является стандартом с 1992 года.
P.P.S .: Я удалил лишний префикс "TABLE_" из имен ваших таблиц. Вам не нужно использовать имя таблицы, чтобы напоминать себе, что это таблица! :-)