使用MAX&的MySQL LEFT JOIN在联接表上使用GROUP BY吗? [英] MySQL LEFT JOIN using MAX & GROUP BY on joined table?

问题描述

我有两个表(成员和活动)，我试图用每个成员的最新活动来查询成员.我已经使用了两个查询(一个用于获取成员，第二个在活动中具有max(id)和group by(member))，以及一些用于合并数据的代码.我敢肯定，可以通过单个查询来完成，但是我不能完全解决.有什么想法吗?

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?

成员表

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

活动表

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

所需结果:

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

推荐答案

每组最新"问题在SQL中非常普遍.仅在此站点上，就有无数解决此问题的示例.

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

如果每个成员活动的时间戳都是唯一的:

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)

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

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

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

您无需分组.但是查询将分别受益于activities或(member_id, 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.

编辑

要显示尚未记录活动的任何成员，请使用像这样的左联接.

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)

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

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.

但是，如果您在联接条件中应用附加谓词权限，则LEFT JOIN将按预期工作.

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

这篇关于使用MAX&的MySQL LEFT JOIN在联接表上使用GROUP BY吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！