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

我需要获取所有人的列表，他们参加了他们上课的第一学期（学期的起点最古老）。

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由子查询计算出来，该子查询按日期对学期进行分类，但返回ID。然后最外面查询重新添加日期。

如果您真的不需要semesterID，则可以避免一层。如果您的学期井井有条，即他们的ID与起始人的顺序相同，那么您可以简单地使用单个查询，就像您自己一样，然后返回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

我将把其他答案作为运动。