mysql - LEFT JOIN 仅来自多对一的最新行

Question

一直在用头撞墙，无法解决这个问题:\

SELECT
  `people`.*,
  `students`.*,
  `student_class_relationships`.*,
  `geo_checkin_on_campus`.`datetime_created` as checkin_time
FROM `student_class_relationships`
  LEFT OUTER JOIN `students`
    ON `student_class_relationships`.`student` = `students`.`id`
  LEFT OUTER JOIN `people`
    ON `students`.`student` = `people`.`id`
  LEFT OUTER JOIN `geo_checkin_on_campus`
    ON `students`.`id` = (
      SELECT MIN(`geo_checkin_on_campus`.`student`)
      FROM `geo_checkin_on_campus`
      WHERE `geo_checkin_on_campus`.`student` = `students`.`id`
    )
 WHERE `class` = 56

预期结果是许多行每个 students.id 只有一个条目。

这是我的 schema

Answer 1

从性能的角度来看，这不是最好的查询，

但只是为了解决您的查询是我的尝试:

SELECT
  `people`.*,
  `students`.*,
  `student_class_relationships`.*,
  geoCheckinOnCampus.datetimeCreated as checkin_time
FROM `student_class_relationships`
  LEFT JOIN `students`
    ON `student_class_relationships`.`student` = `students`.`id`
  LEFT JOIN `people`
    ON `students`.`student` = `people`.`id`
  LEFT JOIN 
    (
      SELECT 
        student,
        MAX(datetime_created) datetimeCreated
      FROM `geo_checkin_on_campus`
      GROUP BY `student`
    ) geoCheckinOnCampus
  ON `students`.`id` = geoCheckinOnCampus.`student`
 WHERE `class` = 56

注意 根据@xQbert 的回答，如果您正在寻找最新的日期时间，我真的会将 MIN 更改为 MAX 函数。