php - 从表中获取多个ID的最后插入数据？

Question

我有一个表“event_activities”，它记录了所有事件的事件。

它在 "events" 表中有一个字段 'event_id' 是 'id'。现在我想检索每个事件的最后插入行，事件应该是不同的。

event_activities
------------------
id eventid date


events
----------
id name place

我尝试过使用 DISTINCT 但我还需要更多字段，我也尝试过使用 group by 但没有成功。

select distinct a.id, a.eventid, a.date, b.name from event_activities as a join events as b on a.eventid = b.id;

和

select a.id, a.eventid, a.date, b.name from event_activities as a join events as b on a.eventid = b.id order by a.eventid;

这两个查询都没有给我所需的结果。有人可以提出一个可以完成我的工作的查询吗。

Answer 1

select a.id, a.eventid, a.date, b.name 
from event_activities as a join events as b on a.eventid = b.id 
where a.date = (select max(date) from event_activities a2 
                where a2.eventid = a.eventid)
order by a.eventid