MySQL LEFT JOIN 在连接表上使用 MAX 和 GROUP BY？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13697607/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL LEFT JOIN using MAX & GROUP BY on joined table?
提问by Shawn McBride
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(member)）和一些用于合并数据的代码。我确信它可以通过一个查询来完成，但我不能完全解决。有任何想法吗？
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
回答by Tomalak
The "latest per group" problem is extremelycommon 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 activitiesover (member_id, timestamp)or (member_id, id), respectively.
你不需要分组。但是查询将分别从activitiesover(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 WHEREclause. Semantically, WHERE is applied afterthe 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 将按预期工作。
回答by edze
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
回答by Dale M
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
