mysql - 选择与两个连接表中的最大值对应的列

Question

我有两个表，分别是 Users 和 Interviews。一个用户可以有多个采访记录。

Users
-----
UserID
FirstName
LastName

Interviews
----------
InterviewID
UserID
DateOfInterview

我只想获取最新的采访记录。这是我的查询

select u.UserID, firstname, lastname, max(DateOfInterview) as latestDOI 
from users u 
left join interviews i 
on u.UserID = i.UserID 
GROUP BY u.UserID, firstname, lastname
ORDER BY max(DateOfInterview) DESC

如何更新查询以同时返回 InterviewID(即对应于 max(DateOfInterview) 的那个)？

Answer 1

您可以在 WHERE 子句中使用聚合子查询，而不是在您的选择列表中使用聚合函数:

select u.UserID, firstname, lastname, i.InterviewId, DateOfInterview as latestDOI 
from users u 
left join interviews i 
  on u.UserID = i.UserID 
where i.UserId is null or i.DateOfInterview = (
  select max(DateOfInterview)
  from interviews i2
  where i2.UserId = u.UserId
)

这确实假设 max(DateOfInterview) 对每个用户都是唯一的，但问题没有明确定义的答案。请注意，主查询不再是聚合查询，因此此类查询的约束不适用。

还有其他方法可以解决这个问题，研究它们是值得的，因为像我现在这样的相关子查询可能是一个性能问题。例如，您可以使用内联 View 生成每个用户最近采访日期的表，并使用连接到该 View 以将用户与他们最近采访的 ID 联系起来:

select u.*, im.latestDOI, i2.InterviewId
from
  users u
  left join (
      select UserID, max(DateOfInterview) as latestDOI 
      from interviews i
      group by UserID
    ) im
    on u.UserId = im.UserId
  left join interviews i2
    on im.UserId = i2.UserId and im.latestDOI = i2.DateOfInterview

还有其他替代方案，一些是标准的，另一些是特定于数据库的。