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

table 上的人:

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

我需要获取所有人的名单，包括他们类的第一个学期(开始日期最早的学期)。

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

你最终可能会使用像下面这样的怪物(fiddle):

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 是通过子查询计算的，子查询按日期对学期进行排序但返回 ID。然后最外层的查询重新添加日期。

如果你真的不需要semesterID，你可以避免一层。如果你的学期是有序的，即他们的 ID 与他们的开始日期有相同的顺序，那么你可以简单地使用一个查询，就像你自己的一样，并返回 min(semesterID) 和 min (开始日期)。

总的来说，这个问题让我想起了很多我自己的问题，Select one value from a group based on order from other columns .建议的答案也可能适用于此。特别是，有一些使用用户变量的方法我仍然感到不舒服，但是这将使整个困惑很多变得更容易并且似乎工作得很好。所以适应this answer , 你会得到这样的查询 ( fiddle ):

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

我将把调整其他答案作为练习。