从 SQL 中选择最上面的结果

Question

我认为这是一个简单的问题，但我似乎无法解决它。

我想从一个表中选择最新的结果并将其与另一个表中的单个元素连接。

更好地说，这是一个简单的模式：

Table 1 - Person
personId -PK - INT - AUTO ID
name - VARCHAR

Table 2 - Event
eventId - PK - INT - AUTO ID
personId - FK
timestamp  - DATETIME
event - ENUM ('Went Out', 'Came back')

我想做的是返回所有人的列表以及每个人执行的最新操作

示例结果：

姓名|人员 ID |时间戳|事件 ID |活动

鲍勃 | 1 | 2011-08-7 3 | “出去了”

我做了一个简单的查询，连接两个表，然后按 personId 进行分组并按时间戳排序，但返回的结果始终是该人的第一个操作，而不是他们的最新操作。

有什么想法吗？

Answer 1

SELECT 
  t1.Name,
  t1.PersonId,
  t2.TimeStamp,
  t2.EventId,
  t2.Event
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.PersonId = t1.PersonID
INNER JOIN (SELECT
              PersonId,
              MAX(TimeStamp) as LastEventDateTime
            FROM Table2 
            GROUP BY PersonID) LE 
  ON LE.PersonID = t2.PersonID 
    AND LE.LastEventDateTime = t2.TimeStamp

Answer 2

SELECT p.name, p.personId, e.timestamp, e.eventId, e.event
FROM person p
  INNER JOIN Event e 
    ON e.eventId = 
      ( SELECT MAX(eventId)
        FROM Event 
        WHERE personId = p.personId 
        GROUP BY personId 
        LIMIT 1 )

或

SELECT p.Name, p.ID, me.timestamp, me.ID, me.event
FROM person p
INNER JOIN (
            SELECT id, timestamp, event
            FROM Event 
            WHERE personId = p.ID               
            ORDER BY timestamp DESC LIMIT 1
           ) me
ON p.ID = me.id

PS：抱歉，但现在无法测试这两个查询

Answer 3

你想要做一个

ORDER by `timestamp` DESC

（降序）来获得最高的时间戳值而不是最低的

Answer 4

ANSI 标准方式是：

select name, personid, timestamp, eventid, event
from person
join event on event.personid=person.personid
  and event.timestamp=(select max(timestamp) from event e2
                       where e2.personid=person.personid)

我有一段时间没有使用 MySQL，而且我没有方便的安装，但你可能会得到你想要的：

select name, personid, timestamp, eventid, event
from person
join event on event.personid=person.personid
group by personid
order by personid, timestamp desc

它是非标准的，因为按照标准，选择中的任何内容都必须是在 group-by 中或作为聚合，这里我们不想这样做。但我记得 MySQL 不需要这样做，所以我会尝试一下，看看会发生什么。

Answer 5

另一种解决方案是使用覆盖密钥，假设 order by Id 会产生与 order by timestamp 相同的结果

SELECT p.Name, p.ID, me.timestamp, me.ID, me.event
FROM person p
JOIN (
      SELECT personId, MAX(ID) id 
      FROM Event 
      WHERE personId = p.ID               
      GROUP BY personId
     ) me
ON p.ID = me.id

Order by timestamp更自然，可能更安全，但速度更快。