MySQL 如何按 MAX（日期）选择？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7836036/
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 SELECT by MAX(date)?
提问by poetter747
This is the table structure
这是表结构
CREATE TABLE `reports` (
`report_id` int(11) NOT NULL auto_increment,
`computer_id` int(11) NOT NULL default '0',
`date_entered` datetime NOT NULL default '1970-01-01 00:00:00',
`total_seconds` int(11) NOT NULL default '0',
`iphone_id` int(11) default '0',
PRIMARY KEY (`report_id`),
KEY `computer_id` (`computer_id`),
KEY `iphone_id` (`iphone_id`)
) ENGINE=MyISAM AUTO_INCREMENT=120990 DEFAULT CHARSET=latin1
I need a SELECTstatement that will list the report_idper computer_idfrom latest entered date_entered, and i have no clue how to do that. Can anyone point me into the right direction? Thx in advance.
我需要一个SELECT语句来列出最新输入的report_idper ，我不知道如何做到这一点。谁能指出我正确的方向？提前谢谢。computer_iddate_entered
回答by bhamby
This should do it:
这应该这样做：
SELECT report_id, computer_id, date_entered
FROM reports AS a
WHERE date_entered = (
SELECT MAX(date_entered)
FROM reports AS b
WHERE a.report_id = b.report_id
AND a.computer_id = b.computer_id
)
回答by Tyler Ferraro
Are you only wanting it to show the last date_entered, or to order by starting with the last_date entered?
您是只想显示最后输入的日期，还是从输入的最后一个日期开始排序？
SELECT report_id, computer_id, date_entered
FROM reports
GROUP BY computer_id
ORDER BY date_entered DESC
-- LIMIT 1 -- uncomment to only show the last date.
回答by Frane Poljak
Accordig to this: https://bugs.mysql.com/bug.php?id=54784casting as char should do the trick:
根据这一点：https://bugs.mysql.com/bug.php?id =54784 cast as char 应该可以解决问题：
SELECT report_id, computer_id, MAX(CAST(date_entered AS CHAR))
FROM reports
GROUP BY report_id, computer_id
回答by LuyRamone
I use this solution having max(date_entered)and it works very well
我使用这个解决方案having max(date_entered)并且效果很好
SELECT
report_id,
computer_id,
date_entered
FROM reports
GROUP BY computer_id having max(date_entered)
回答by xayer
Works perfect for me:
非常适合我：
(SELECT content FROM tblopportunitycomments WHERE opportunityid = 1 ORDER BY dateadded DESC LIMIT 1);
回答by Jeremy
This is a very old question but I came here due to the same issue, so I am leaving this here to help any others.
这是一个非常古老的问题，但我是因为同样的问题来到这里的，所以我把它留在这里是为了帮助其他人。
I was trying to optimize the query because it was taking over 5 minutes to query the DB due to the amount of data. My query was similar to the accepted answer's query. Pablo's commentpushed me in the right direction and my 5 minute query became 0.016 seconds. So to help any others that are having very long query times try using an uncorrelated subquery.
我试图优化查询，因为由于数据量，查询数据库需要 5 多分钟。我的查询类似于接受的答案的查询。Pablo 的评论将我推向了正确的方向，我的 5 分钟查询变成了 0.016 秒。因此，为了帮助任何其他查询时间很长的人，请尝试使用不相关的子查询。
The example for the OP would be:
OP 的示例是：
SELECT
a.report_id,
a.computer_id,
a.date_entered
FROM reports AS a
JOIN (
SELECT report_id, computer_id, MAX(date_entered) as max_date_entered
FROM reports
GROUP BY report_id, computer_id
) as b
WHERE a.report_id = b.report_id
AND a.computer_id = b.computer_id
AND a.date_entered = b.max_date_entered
Thank you Pablofor the comment. You saved me big time!
谢谢巴勃罗的评论。你救了我很多时间！
回答by Gianluca Demarinis
Workaround but working solution
解决方法但有效的解决方案
Only if ID is autoincrement, you can search for the maximum id instead of the max date. So, by the ID you can find all others fields.
仅当 ID 为 autoincrement 时，您可以搜索最大 id 而不是最大日期。因此，通过 ID，您可以找到所有其他字段。
select *
from table
where id IN (
select max(id)
from table
group by #MY_FIELD#
)
回答by east
It works great for me
这对我很有效
SELECT report_id,computer_id,MAX(date_entered) FROM reports GROUP BY computer_id
SELECT report_id,computer_id,MAX(date_entered) FROM reports GROUP BY computer_id
回答by sixstring
Did this on a blog engine to get the latest blog. I adapted it to your table structure.
在博客引擎上执行此操作以获取最新博客。我根据你的表结构调整了它。
SELECT * FROM reports WHERE date_entered = (SELECT max(date_entered) FROM REPORTS)
回答by Daniel Gray
SELECT report_id, computer_id, date_entered
FROM reports
WHERE date_entered = (
SELECT date_entered
FROM reports
ORDER date_entered
DESC LIMIT 1
)
