MySQL 选择表中的最新记录（日期时间字段）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4788644/
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
Select latest record in table (datetime field)
提问by GSTAR
I have searched the site for assistance but still struggling. Here is my table:
我已经搜索了该网站以寻求帮助，但仍在挣扎。这是我的表：
messages ======== id thread_id user_id subject body date_sent
Basically I want to retrieve the latest record for each thread_id. I have tried the following:
基本上我想检索每个thread_id的最新记录。我尝试了以下方法：
SELECT id, thread_id, user_id, subject, body, date_sent
FROM messages
WHERE user_id=1 AND date_sent=(select max(date_sent))
GROUP BY thread_id
ORDER BY date_sent DESC
BUT it is giving me the oldest records, not the newest!
但它给了我最古老的记录，而不是最新的！
Anyone able to advise?
任何人都可以建议？
EDIT: Table dump:
编辑：表转储：
-- -- Table structure for table `messages` -- CREATE TABLE IF NOT EXISTS `messages` ( `id` int(10) unsigned NOT NULL auto_increment, `thread_id` int(10) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `body` text NOT NULL, `date_sent` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ; -- -- Dumping data for table `messages` -- INSERT INTO `messages` (`id`, `thread_id`, `user_id`, `body`, `date_sent`) VALUES (1, 1, 1, 'Test Message', '2011-01-20 00:13:51'), (2, 1, 6, 'Test Message', '2011-01-20 01:03:50'), (3, 1, 6, 'Test Message', '2011-01-20 01:22:52'), (4, 1, 6, 'Test Message', '2011-01-20 11:59:01'), (5, 1, 1, 'Test Message', '2011-01-20 11:59:22'), (6, 1, 6, 'Test Message', '2011-01-20 12:10:37'), (7, 1, 1, 'Test Message', '2011-01-20 12:10:51'), (8, 2, 6, 'Test Message', '2011-01-20 12:45:29'), (9, 1, 6, 'Test Message', '2011-01-20 13:08:42'), (10, 1, 1, 'Test Message', '2011-01-20 13:09:49'), (11, 2, 1, 'Test Message', '2011-01-20 13:10:17'), (12, 3, 1, 'Test Message', '2011-01-20 13:11:09'), (13, 1, 1, 'Test Message', '2011-01-21 02:31:43'), (14, 2, 1, 'Test Message', '2011-01-21 02:31:52'), (15, 4, 1, 'Test Message', '2011-01-21 02:31:57'), (16, 3, 1, 'Test Message', '2011-01-21 02:32:10'), (17, 4, 6, 'Test Message', '2011-01-20 22:36:57'), (20, 1, 6, 'Test Message', '2011-01-20 23:02:36'), (21, 4, 1, 'Test Message', '2011-01-20 23:17:22');
EDIT: Apologies - I may have got things slightly confused here - basically what I want is to retrieve all messages for a given user_id, THEN find the latest message (per thread_id) from those retrieved messages.
编辑：抱歉 - 我可能在这里有点困惑 - 基本上我想要的是检索给定 user_id 的所有消息，然后从这些检索到的消息中找到最新的消息（每个 thread_id）。
回答by Ashraf Abrahams
SELECT id, thread_id, user_id, subject, body, date_sent
FROM messages WHERE date_sent IN (
SELECT MAX( date_sent )
FROM messages WHERE user_id =6 GROUP BY thread_id
)
ORDER BY thread_id ASC , date_sent DESC;
Let me know if it works now
让我知道它现在是否有效
回答by Mchl
It's a two stop process. First find the newest dates for each thread_id. Then select records that have these dates and matching thread_ids
这是一个两站的过程。首先找到每个 的最新日期thread_id。然后选择具有这些日期和匹配thread_ids 的记录
SELECT t.id, t.thread_id, t.user_id, t.body, t.date_sent
FROM messages AS t
CROSS JOIN (
SELECT thread_id, MAX(date_sent) AS date_sent FROM messages WHERE user_id = 1 GROUP BY thread_id
) AS sq
USING (thread_id, date_sent)
Note that if two (or more) messageshave same date_sentand same thread_idthey will both be selected (because you can't tell which one is newer)
请注意，如果两个（或多个）messages相同date_sent且相同，则thread_id它们都将被选中（因为您无法分辨哪个是较新的）
回答by PaulC
This is a really old question but anyway...
这是一个非常古老的问题，但无论如何......
Your where clause isn't specific enough, and using date_sent to select the right record is just wrong. Try this:
您的 where 子句不够具体，使用 date_sent 选择正确的记录是错误的。尝试这个：
SELECT id, thread_id, user_id, subject, body, date_sent
FROM messages
WHERE id=(
select m2.id from messages m2
where messages.thread_id=m2.thread_id
order by date_sent desc limit 1)
ORDER BY date_sent DESC
If you want to assume that id always increases over time, this would probably perform better:
如果你想假设 id 总是随着时间的推移而增加，这可能会表现得更好：
SELECT id, thread_id, user_id, subject, body, date_sent
FROM messages
WHERE id in (
select max(m2.id) from messages m2 group by m2.thread_id)
ORDER BY date_sent DESC
回答by Ashraf Abrahams
From what I can see, your problem lies with the sub-query. The sub-query will actually be extracting the maximum date_sent field from the current record, in other words, as the outer query traverses the table one record at a time the two date_sent fields in the sub-query "date_sent=(select max(date_sent)" will always be the same. After it displays the first record for a particular thread_id, it doesn't show any other records for that thread_id since you grouping by thread_id. This is why, it will always show the first record entered for each thread_id. BTW, it's showing the first record entered for each thread_id and not the earliest date_sent record. Your result is dependent on the location of the record in your table and not on the value of date_sent. Not sure if I explained this correctly, but anyway, to fix your problem try:
据我所知，您的问题在于子查询。子查询实际上会从当前记录中提取最大的 date_sent 字段，换句话说，当外部查询一次遍历表一条记录时，子查询“ date_sent=(select max(date_sent)”中的两个 date_sent 字段将始终相同。在显示特定 thread_id 的第一条记录后，它不会显示该 thread_id 的任何其他记录，因为您按 thread_id 分组。这就是为什么它总是显示为每个 thread_id 输入的第一条记录。顺便说一句，它显示了为每个 thread_id 输入的第一条记录，而不是最早的 date_sent 记录。您的结果取决于表中记录的位置，而不取决于 date_sent 的值。不确定我是否正确解释了这一点，但无论如何，要解决您的问题，请尝试：
SELECT id, thread_id, user_id, subject, body, date_sent
FROM messages
WHERE user_id=1 AND date_sent IN (select max(date_sent) from messages GROUP BY thread_id)
GROUP BY thread_id
ORDER BY date_sent DESC;
Firstly, the sub-query must have a FROMclause and a GROUP BYclause to pull up the maximum dates per thread_id from the WHOLE table and not just the current record. Also, the =must be replaced with an INsince the sub-query might result in multiple records. If the table contains two records of the same thread-id on the same date, only the first one will be displayed. This is caused by the second GROUP BYclause in the outer query. To display all the records for that thread_id on that day, try:
首先，子查询必须有一个FROM子句和一个GROUP BY子句来从 WHOLE 表中提取每个 thread_id 的最大日期，而不仅仅是当前记录。此外，=必须用 替换，IN因为子查询可能会产生多条记录。如果该表在同一日期包含同一线程 ID 的两条记录，则仅显示第一条记录。这是由GROUP BY外部查询中的第二个子句引起的。要显示当天该 thread_id 的所有记录，请尝试：
SELECT id, thread_id, user_id, subject, body, date_sent
FROM messages
WHERE user_id =1 AND date_sent IN (SELECT MAX( date_sent ) FROM messages GROUP BY thread_id)
ORDER BY thread_id ASC , date_sent DESC;
By removing the second GROUP BYclause and adding an ORDER BYclause, you can show all messages for that maximum date for each thread_id and still display the threads in the correct order. Hope that helps.
通过删除第二个GROUP BY子句并添加一个ORDER BY子句，您可以为每个 thread_id 显示该最大日期的所有消息，并且仍然以正确的顺序显示线程。希望有帮助。
