MySQL 如何使用GROUP BY获取每个组中的最新记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10999522/
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
How to get the latest record in each group using GROUP BY?
提问by user1019144
Let's say I have a table called messageswith the columns:
假设我有一个messages用列调用的表：
id | from_id | to_id | subject | message | timestamp
I want to get the latest message from each user only, like you would see in your FaceBook inbox before you drill down into the actual thread.
我只想从每个用户那里获取最新消息，就像您在深入了解实际线程之前在 Facebook 收件箱中看到的那样。
This query seems to get me close to the result I need:
这个查询似乎让我接近我需要的结果：
SELECT * FROM messages GROUP BY from_id
However the query is giving me the oldest message from each user and not the newest.
但是，查询给了我来自每个用户的最旧消息，而不是最新消息。
I can't figure this one out.
我无法弄清楚这一点。
回答by Devart
You should find out last timestampvalues in each group (subquery), and then join this subquery to the table -
您应该找出timestamp每个组（子查询）中的最后一个值，然后将此子查询加入表中 -
SELECT t1.* FROM messages t1
JOIN (SELECT from_id, MAX(timestamp) timestamp FROM messages GROUP BY from_id) t2
ON t1.from_id = t2.from_id AND t1.timestamp = t2.timestamp;
回答by Venu Morigadi
Try this
尝试这个
SELECT * FROM messages where id in (SELECT max(id) FROM messages GROUP BY from_id ) order by id desc
回答by leyla azari
this query return last record for every Form_id:
此查询为每个 Form_id 返回最后一条记录：
SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
ON (m1.Form_id = m2.Form_id AND m1.id < m2.id)
WHERE m2.id IS NULL;
回答by Jonathan Leffler
This is a standard problem.
这是一个标准问题。
Note that MySQL allows you to omit columns from the GROUP BY clause, which Standard SQL does not, but you do not get deterministic results in general when you use the MySQL facility.
请注意，MySQL 允许您从 GROUP BY 子句中省略列，而标准 SQL 则不允许，但是当您使用 MySQL 工具时，通常不会得到确定性的结果。
SELECT *
FROM Messages AS M
JOIN (SELECT To_ID, From_ID, MAX(TimeStamp) AS Most_Recent
FROM Messages
WHERE To_ID = 12345678
GROUP BY From_ID
) AS R
ON R.To_ID = M.To_ID AND R.From_ID = M.From_ID AND R.Most_Recent = M.TimeStamp
WHERE M.To_ID = 12345678
I've added a filter on the To_IDto match what you're likely to have. The query will work without it, but will return a lot more data in general. The condition should not need to be stated in both the nested query and the outer query (the optimizer should push the condition down automatically), but it can do no harm to repeat the condition as shown.
我在 上添加了一个过滤器To_ID以匹配您可能拥有的内容。没有它，查询也能工作，但通常会返回更多的数据。条件不需要在嵌套查询和外部查询中都声明（优化器应该自动下推条件），但是如图所示重复条件没有坏处。
回答by Bruno de Oliveira
Just complementing what Devart said, the below code is not ordering according to the question:
只是补充德瓦特所说的，下面的代码不是根据问题排序的：
SELECT t1.* FROM messages t1
JOIN (SELECT from_id, MAX(timestamp) timestamp FROM messages GROUP BY from_id) t2
ON t1.from_id = t2.from_id AND t1.timestamp = t2.timestamp;
The "GROUP BY" clause must be in the main query since that we need first reorder the "SOURCE" to get the needed "grouping" so:
“GROUP BY”子句必须在主查询中，因为我们需要首先对“SOURCE”重新排序以获得所需的“分组”，因此：
SELECT t1.* FROM messages t1
JOIN (SELECT from_id, MAX(timestamp) timestamp FROM messages ORDER BY timestamp DESC) t2
ON t1.from_id = t2.from_id AND t1.timestamp = t2.timestamp GROUP BY t2.timestamp;
Regards,
问候，
回答by Marcus Recck
You need to order them.
你需要订购它们。
SELECT * FROM messages GROUP BY from_id ORDER BY timestamp DESC LIMIT 1
SELECT * FROM messages GROUP BY from_id ORDER BY timestamp DESC LIMIT 1
