mysql - 从连接表中仅选择具有 MAX(date) 的行中的所有列

Question

我有 3 张 table :

events
+--------------------------------+
| id | date       | Name         |
+--------------------------------+
| 1  | 2019-09-29 | First Event  |
| 2  | 2019-10-05 | Second Event |
| 3  | 2019-10-11 | Third Event  |
+--------------------------------+

files
+-----------------------------+
| id | name        | fileSize |
+-----------------------------+
| 1  | 1 file name | 1234786  |
| 2  | 2 file name | 6127833  |
| 3  | 3 file name | 1207834  |
| 4  | 4 file name | 2912873  |
| 5  | 5 file name | 7893465  |
+-----------------------------+

agendaItems
+-----------------------+
| id | eventId | fildId |
+-----------------------+
| 1  | 1       | 1      |
| 2  | 1       | 4      |
| 3  | 1       | 2      |
| 4  | 1       | 5      |
| 5  | 1       | 1      |
| 6  | 2       | 3      |
| 7  | 2       | 4      |
| 8  | 2       | 3      |
| 9  | 2       | 3      |
| 10 | 2       | 2      |
| 11 | 3       | 1      |
| 12 | 3       | 3      |
| 13 | 3       | 5      |
| 14 | 3       | 4      |
| 15 | 3       | 1      |
| 16 | 2       | 2      |
| 17 | 1       | 3      |
| 18 | 3       | 4      |
+-----------------------+

如果用户在文件中搜索字符串 file 并返回 5 个文件，我将如何显示每个文件所属的最新事件(id/日期/名称)以及所有文件信息？

我已经研究了几个小时了，如果没有 3 级深子查询，似乎无法找到解决方案。如果我使用 GROUP BY 加入议程项目和事件，然后选择 MAX(events.date) 我可以获得正确的日期，但名称和 ID 并不总是正确的...

问题似乎是文件和事件之间没有直接关系，因为一个文件可以添加到事件中的多个议程项目(例如文件 1 位于事件中) 1 在议程项目 1 和 5 中两次)。我在这里看到的所有其他帖子都不适用于这种情况。

我觉得我在这里遗漏了一些非常明显的东西。任何帮助都会很棒。

编辑 1:到目前为止，我的解决方案是执行一个子查询来获取每个文件所属的所有事件，然后我可以从中进行选择以获取每个文件的 MAX(events.date) ，然后将这些事件保留到主查询中这是获取文件信息。

但这似乎确实效率低下，尤其是从长远来看。将来，我每次都必须选择潜在的数千个文件/事件组合，才能获得最后一次 5 个文件成为事件一部分的时间。

Answer 1

这个查询应该会给你你想要的结果。它将files表JOIN到每个fileId的最新事件日期列表(通过JOIN找到agendaItems 和 events)，然后将其JOIN到 agendaItems 和 events 表中获取适当的事件名称:

SELECT f.*, a.Name
FROM files f
JOIN (SELECT fileId, MAX(e.date) AS date
      FROM agendaItems a
      JOIN events e ON e.id = a.eventId
      GROUP BY fileId) m ON m.fileId = f.id
JOIN (SELECT DISTINCT a.fileId, a.eventId, e.Name, e.date
      FROM agendaItems a 
      JOIN events e ON e.id = a.eventId) a ON a.fileId = f.id AND a.date = m.date

输出:

id  name            fileSize    Name
1   1 file name     1234786     Third Event
2   2 file name     6127833     Second Event
3   3 file name     1207834     Third Event
4   4 file name     2912873     Third Event
5   5 file name     7893465     Third Event

Demo on dbfiddle