MySQL - вернуть строки в одной таблице, которые соответствуют минимальной дате в другой (косвенно связанной) таблице

Question

Столовые семестры:

semesterID  startDate
1           2013-01-01
2           2013-03-01
3           2013-06-01

Табличные занятия:

classID  class_title  semesterID
1        Math         1
2        Science      1
3        Math         2
4        Science      2
5        Math         3
6        Science      3

Стол персоны:

personID  firstName  lastName
1         John       Jones
2         Steve      Smith

Таблица class_person:

classID  personID
1        1
2        1
5        1
6        1
3        2
4        2
5        2
6        2

Мне нужно получить список всех людей, с первого семестра, в котором они взяли класс (семестр с самой старой startDate).

firstName,  lastName, semesterID, startDate
John        Jones     1           2013-01-01
Steve       Smith     2           2013-03-01

Я часами пытался понять это. Вот самое близкое, что я получил (хотя это совсем не близко!):

SELECT p.firstName, p.lastName, MIN(s.startDate) AS min_startDate
FROM semesters s
INNER JOIN classes c ON s.semesterID = c.semesterID
INNER JOIN class_person cp ON cp.classID = c.classID
INNER JOIN persons p ON p.personID = cp.personID
GROUP BY cs.personID
ORDER BY min_startDate, p.lastName, p.firstName

Любая помощь будет высоко ценится. Спасибо.

Answer 1

Вы можете использовать монстра, как показано ниже ( скрипка):

select persons.firstName, persons.lastName,
       semesters.semesterID, semesters.startDate
from persons, semesters,
(select p.personID,
 (select semesters.semesterID
  from semesters, classes, class_person
  where semesters.semesterID = classes.semesterID
    and classes.classID = class_person.classID
    and class_person.personID = p.personID
  order by semesters.startDate
  limit 1) as semesterID
 from (select distinct personID from class_person) as p
) as ps
where persons.personID = ps.personID
  and semesters.semesterID = ps.semesterID

Подзапрос p идентифицирует всех людей. Для каждого, ps будет содержать одну строку. этоpersonIDпросто копируется, егоsemesterIDвычисляется подзапросом, который сортирует семестры по дате, но возвращает идентификатор. Внешний запрос затем повторно добавляет дату.

Если вам не нужно semesterIDВы могли бы избежать одного слоя. Если ваши семестры в порядке, т.е. их идентификаторы имеют тот же порядок, что и их startDates, то вы можете просто использовать один запрос, очень похожий на ваш, и вернутьmin(semesterID)а такжеmin(startDate),

В целом, этот вопрос очень напоминает мне мой собственный вопрос. Выберите одно значение из группы на основе порядка из других столбцов. Ответы, предложенные там, вероятно, применимы и здесь. В частности, есть подходы, использующие пользовательские переменные, которые мне все еще не нравятся, но которые значительно облегчат весь этот беспорядок и, кажется, будут работать достаточно хорошо. Таким образом, адаптируя этот ответ, вы получите запрос, подобный этому ( скрипка):

SELECT p.firstName, p.lastName, s2.semesterID, s2.startDate
FROM persons p
INNER JOIN (
 SELECT @rowNum:=IF(@personID=cp.personID,@rowNum+1,1) rowNum,
        @personId:=cp.personID personID,
        s.semesterID, s.startDate
 FROM (SELECT @personID:=NULL,@rowNum:=0) dummy
 INNER JOIN semesters s
 INNER JOIN classes c ON s.semesterID = c.semesterID
 INNER JOIN class_person cp ON cp.classID = c.classID
 ORDER BY cp.personID, s.startDate
) s2 ON p.personID = s2.personID
WHERE s2.rowNum = 1

Я оставлю адаптацию других ответов в качестве упражнения.