mysql - 左连接最新记录(如果存在)

Question

我有一张人员表，我想提取最新的事件记录，但可能没有。

所以我的问题是，即使我留下加入以确保我获得每个人的记录，我也只会带回有事件的人，因为我将日期的最大值作为标准。

SELECT *
FROM tbl_people
LEFT JOIN tbl_events ON
    tbl_people.People_UID = tbl_events.People_UID
WHERE tbl_people.Active = 1
AND (
    SELECT MAX(Event_Date) FROM tbl_events
    )

表人员将具有以下内容

People_UID

表事件将具有以下内容

Event_UID, People_UID, Event_Name, Event_Date

就像我说的，我想要的是这样的输出:

Jen, Had a Baby, 7/10/2015
Shirley
Susan
Megan, Had a Baby, 8/5/2014
etc.

我希望这是有道理的。

Answer 1

select * from 
(  SELECT *, row_number() over (partition by tbl_people.name order by tbl_events.Event_Date desc) as rn 
     FROM tbl_people
     LEFT JOIN tbl_events 
       ON tbl_people.People_UID = tbl_events.People_UID
    WHERE tbl_people.Active = 1 ) t 
where t.rn = 1