MySQL左连接使用MAX & GROUP ?

I've got two tables (members and activities) and I'm trying to query the members with the latest activity for each member. I've got it working with two queries (one to get the members and a second with a max(id) and group by(member) on the activities) and some code to merge the data. I'm SURE it can be done with a single query, but I can't quite work it out. Any ideas?

我有两个表（成员和活动），我正在尝试使用每个成员的最新活动查询成员。我已经使用了两个查询（一个用于获取成员，第二个用max（id）和group by（成员）在活动上）和一些代码来合并数据。我确定它可以通过一个查询来完成，但我无法完成它。有任何想法吗？

members table

成员表

id, name
 1, Shawn
 2, bob
 3, tom

activities table

活动表

id, member_id, code, timestamp, description
 1,         1,  123,     15000, baked a cake
 2,         1,  456,     20000, ate dinner
 3,         2,  789,     21000, drove home
 4,         1,  012,     22000, ate dessert

desired result:

期望的结果：

id, name,  activity_code, activity_timestamp, activity_description
 1, shawn, 012,           22000,              ate dessert
 2, bob,   789,           21000,              drove home
 3, tom,   null,          null,               null

3 个解决方案

#1

The "latest per group" problem is extremely common in SQL. There are countless examples of solutions to this very problem on this site alone.

“每组最新”问题在SQL中非常常见。仅在这个网站上有无数解决这个问题的例子。

If your timestamps are uniqe per member activity:

如果您的时间戳是每个成员活动的独立时间：

SELECT
  m.id,
  m.name,
  a.code activity_code,
  a.timestamp activity_timestamp,
  a.description activity_description
FROM
  members m
  INNER JOIN activities a ON a.member_id = m.id
WHERE
  a.timestamp = (SELECT MAX(timestamp) FROM activities WHERE member_id = m.id)

alternatively, if your activity ID is increasing monotonically with time:

或者，如果您的活动ID随时间单调增加：

  ...
WHERE
  a.id = (SELECT MAX(id) FROM activities WHERE member_id = m.id)

You don't need to group. But the query will benefit from an index on activities over (member_id, timestamp) or (member_id, id), respectively.

你不需要分组。但是查询将分别受益于（member_id，timestamp）或（member_id，id）上的活动索引。

EDIT

编辑

To show any members who have not logged an activity, use a left join like this.

要显示未记录活动的任何成员，请使用这样的左连接。

SELECT
  m.id,
  m.name,
  a.code activity_code,
  a.timestamp activity_timestamp,
  a.description activity_description
FROM
  members m
  LEFT JOIN activities a ON 
    a.member_id = m.id
    AND a.timestamp = (SELECT MAX(timestamp) FROM activities WHERE member_id = m.id)

Note that there is no WHERE clause. Semantically, WHERE is applied after the joins are done. So a WHERE clause would remove the rows that the LEFT JOIN added, effectively giving in the same result as the original INNER JOIN.

请注意，没有WHERE子句。在语义上，在完成连接之后应用WHERE。因此，WHERE子句将删除LEFT JOIN添加的行，从而有效地给出与原始INNER JOIN相同的结果。

But if you apply the additional predicate right in the join condition, the LEFT JOIN will work as expected.

但是如果在连接条件中应用附加谓词，则LEFT JOIN将按预期工作。

#2

SELECT 
    members.id ,
    members.name,
    activities.code AS activity_code,
    activities.timestamp AS activity_timestamp,
    activities.description AS activity_description
FROM 
    members
    LEFT JOIN activities
        ON members.id = activities.member_id
    LEFT JOIN 
        (
            SELECT
                activities.member_id
                MAX(activities.id) AS id
            FROM activities
            GROUP BY 
                activities.member_id
        ) AS t1
        ON activities.id = t1.id
WHERE
    t1.id IS NOT NULL

#3

Select max(a.id), m.name, a.activity_code, a.activity_timestamp, a.activity_description
From members m
     Left join
     activities a on a.member_id=m.id
Group by  m.name, a.activity_code, a.activity_timestamp, a.activity_description

#1