mySQL 查询 - 如果连接表中的最新记录包含特定值，则选择连接

Question

我正在尝试编写一个带有正确连接(对客户端)的选择语句，它将在连接表中找到一个特定值——但前提是这是每个客户端的最新值(忽略空白和空值)。

  Clients
  Id    Name
   0     John Doe
   1     Frank Smith
   2     Sue Smith
   3     John Smith


 Activity   (join table)
 ClientId  Type   Date
   0       500   2013-01-01 00:00:08
   1       900   2013-01-01 00:00:07
   2       NULL  2013-01-01 00:00:06
   3             2013-01-01 00:00:05
   4       500   2013-01-01 00:00:05
   0       800   2013-01-01 00:00:04
   1       500   2013-01-01 00:00:03
   2       500   2013-01-01 00:00:02
   3       500   2013-01-01 00:00:01
   4       800   2013-01-01 00:00:00

所以这个查询至少会给我事件类型为 500 的客户记录(在这种情况下我会返回客户 0 和 4):

select * from clients right join activity on activity.clientid = clients.id 
where activity.type = 500

但是，我需要弄清楚如何只返回上述记录列表中的第一条记录。客户端 #0 的逻辑是唯一具有 500 的客户端，因为它的最新事件类型 = 500。其他 3 个客户端的“最新”事件类型为 NULL、空白或 900。

我在想一些神奇的排序(日期通常会非常准确)、“最高”和/或“限制”以及可能的合并？只是无法完全理解它。

Answer 1

请试试这个

SELECT activity.id AS activityid
    , activity.type
    , activity.date
    , clients.id AS clientid
    , clients.name
FROM activity
LEFT JOIN activity AS other_activities
  ON activity.ClientID  = other_activities.ClientID
    AND activity.date < other_activities.date
LEFT JOIN clients
  ON activity.ClientID = clients.id
WHERE activity.type = 500
  AND other_activities.ClientID IS NULL;