限制一次连接的 MySQL 多连接查询

Question

我有一个正在处理的 MYSQL 查询，它从多个连接中提取数据。

select students.studentID, students.firstName, students.lastName, userAccounts.userID, userstudentrelationship.userID, userstudentrelationship.studentID, userAccounts.getTexts, reports.pupID, contacts.pfirstName, contacts.plastName, reports.timestamp

 from userstudentrelationship  

join userAccounts on (userstudentrelationship.userID = userAccounts.userID)
join students on (userstudentrelationship.studentID = students.studentID) 
join reports on (students.studentID = reports.studentID) 
join contacts on (reports.pupID = contacts.pupID) 

where userstudentrelationship.studentID = "10000005" AND userAccounts.getTexts = 1 ORDER BY reports.timestamp DESC LIMIT 1

我有一个独特的情况，我希望其中一个连接(报告连接)仅限于该表的最新结果(我使用的是 order by reports.timestamp desc limit 1)，但不限制整个查询的结果数量。

通过运行上面的查询，我得到了我期望的数据，但是当它应该返回多条记录时只有一条记录。

我的问题:

如何修改此查询以确保我收到所有可能的可用记录，同时确保仅使用报告连接中的最新记录？我预计每条记录可能包含来自其他连接的不同数据，但此查询返回的所有记录将共享相同的报告记录

Answer 1

前提是我理解这个问题；可以向一组数据(下面的别名 Z)添加一个连接，该数据具有每个学生的最大时间戳；从而限制每个学生的一份报告记录(最近的)。

SELECT students.studentID
     , students.firstName
     , students.lastName
     , userAccounts.userID
     , userstudentrelationship.userID
     , userstudentrelationship.studentID
     , userAccounts.getTexts
     , reports.pupID
     , contacts.pfirstName
     , contacts.plastName
     , reports.timestamp
FROM userstudentrelationship  
join userAccounts 
  on userstudentrelationship.userID = userAccounts.userID
join students 
  on userstudentrelationship.studentID = students.studentID
join reports 
  on students.studentID = reports.studentID
join contacts 
  on reports.pupID = contacts.pupID
join (SELECT max(timestamp) mts, studentID 
      FROM REPORTS 
      GROUP BY StudentID) Z
  on reports.studentID = Z.studentID
 and reports.timestamp = Z.mts
WHERE userstudentrelationship.studentID = "10000005" 
  AND userAccounts.getTexts = 1 
ORDER BY reports.timestamp