mysql - 无法在 join 中获得 MAX 时间，但具有不同的 id MySQL

Question

我有 3 个表来获得所需的输出(最新文件路径):-

1. 事件表[具有事件 ID]
2. Event_media 表 [具有事件 ID、媒体 ID 和创建时间]
3. 媒体表[具有媒体 ID 和文件路径]

I want to get- all distinct events with their filepath where creation_time of event media Table is MAX such that i can have all events with their updated filepath

MySQL 查询:-

select e.event_id, m.filepath, em.creation_time as latest from event e join event_media em on e.event_id=em.event_id join media m on em.media_id = m.media_id where em.creation_time=(select MAX(em.creation_time) from event_media where em.event_id =e.event_id);

输出:-

+----------+------------------------------------------------------------------------------------+---------------------+
| event_id | filepath                                                                           | latest              |
+----------+------------------------------------------------------------------------------------+---------------------+
|  1000055 | http://localhost:3000/static/images/glasoimage/event1.jpg                          | 2017-07-06 02:06:30 |
|  1000056 | http://localhost:3000/static/images/glasoimage/event2.jpg                          | 2017-07-06 02:15:15 |
|  1000058 | http://localhost:3000/static/images/glasoimage/event3.jpg                          | 2017-07-06 02:22:17 |
|  1000059 | http://localhost:3000/static/uploads/media/upload_b46a5d6f37f1c77a17b87fcbe5ccb975 | 2017-07-06 02:23:17 |
|  1000066 | http://localhost:3000/static/images/glasoimage/event6.jpg                          | 2017-07-06 17:10:59 |
|  1000057 | http://localhost:3000/static/uploads/media/upload_12b7a15dfae2ce4f7864c957b8ecf5a6 | 2017-07-06 02:20:52 |
|  1000062 | http://localhost:3000/static/images/glasoimage/event4.jpg                          | 2017-07-06 16:45:22 |
|  1000063 | http://localhost:3000/static/images/glasoimage/event5.jpg                          | 2017-07-06 16:47:30 |
|  1000071 | http://localhost:3000/static/uploads/media/upload_d00ab7878fcf6bacdbf800249678b818 | 2017-07-15 14:10:36 |
|  1000071 | http://localhost:3000/static/uploads/media/upload_0c85d276316550ef33e2f274635b99c7 | 2017-07-15 23:42:03 |
|  1000072 | http://localhost:3000/static/images/defaults/default-image.jpg                     | 2017-07-15 23:29:24 |
|  1000073 | http://localhost:3000/static/images/defaults/default-image.jpg                     | 2017-07-16 12:21:57 |
|  1000074 | http://localhost:3000/static/images/defaults/default-image.jpg                     | 2017-07-16 12:25:09 |
|  1000075 | http://localhost:3000/static/images/defaults/default-image.jpg                     | 2017-07-16 13:40:11 |
|  1000076 | http://localhost:3000/static/images/defaults/default-image.jpg                     | 2017-07-16 13:43:10 |
|  1000077 | http://localhost:3000/static/images/defaults/default-image.jpg                     | 2017-07-16 14:04:58 |
|  1000067 | http://localhost:3000/static/images/defaults/default-image.jpg                     | 2017-07-13 21:55:21 |
|  1000068 | http://localhost:3000/static/uploads/media/upload_d32e3685c22232d00602e827f55f6f64 | 2017-07-13 22:45:04 |
|  1000069 | http://localhost:3000/static/uploads/media/upload_e0aab7ea8150ee5e7e1f55d98366e51f | 2017-07-13 22:47:50 |
+----------+------------------------------------------------------------------------------------+---------------------+
19 rows in set (0.18 sec)

对于输出中第 9 行和第 10 行的事件 ID 1000071，我有两个文件路径:旧文件路径和新文件路径。

I want the new Filepath only with no duplication of event Id

提前致谢

Answer 1

您可以使用 group by 和 max 连接

  select 
      e.event_id
      , m.filepath
      , em.creation_time as latest 
  from event e 
  join event_media em on e.event_id=em.event_id 
  join media m on em.media_id = m.media_id 
  join ( 
      select 
           event_id
          , MAX(creation_time) as max_time
      from event_media 
      group by event_id
    ) t on t.event_id = em-event_id and em.creation_time = t.max_time