从分组的 MySQL 数据中获取最新日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8470311/
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
Get the latest date from grouped MySQL data
提问by nunu
I have the following data in my database:
我的数据库中有以下数据：
|NO | model | date |
+---+-------+----------+
|1 | bee |2011-12-01|
|2 | bee |2011-12-05|
|3 | bee |2011-12-12|
|4 | tar |2011-12-13|
I want to get the latest date of each model group:
我想获取每个模型组的最新日期：
| model | date |
+-------+----------+
| bee |2011-12-12|
| tar |2011-12-13|
I tried:
我试过：
SELECT model, date
FROM doc
WHERE date ........????? //what is the next?
GROUP BY model
回答by phatfingers
Are you looking for the max date for each model?
您是否正在寻找每个型号的最大日期？
SELECT model, max(date) FROM doc
GROUP BY model
If you're looking for all models matching the max date of the entire table...
如果您正在寻找与整个表的最大日期匹配的所有模型......
SELECT model, date FROM doc
WHERE date IN (SELECT max(date) FROM doc)
[--- Added ---]
[ - - 添加 - -]
For those who want to display details from every record matching the latest date within each model group (not summary data, as asked for in the OP):
对于那些想要显示每个模型组中与最新日期匹配的每条记录的详细信息的人（不是 OP 中要求的汇总数据）：
SELECT d.model, d.date, d.color, d.etc FROM doc d
WHERE d.date IN (SELECT max(d2.date) FROM doc d2 WHERE d2.model=d.model)
MySQL 8.0 and newer supports the OVERclause, producing the same results a bit faster for larger data sets.
MySQL 8.0 及更新版本支持该OVER子句，对于较大的数据集产生相同的结果会更快一些。
SELECT model, date, color, etc FROM (SELECT model, date, color, etc,
max(date) OVER (PARTITION BY model) max_date FROM doc) predoc
WHERE date=max_date;
回答by gprathour
You can try using max() in subquery, something like this :
您可以尝试在子查询中使用 max()，如下所示：
SELECT model, date
FROM doc
WHERE date in (SELECT MAX(date) from doc GROUP BY model);
回答by Madhu Kiran Seelam
Subquery giving dates. We are not linking with the model. So below query solves the problem.
子查询给出日期。我们没有与模型链接。所以下面的查询解决了这个问题。
If there are duplicate dates/model can be avoided by the following query.
如果有重复的日期/模型可以通过以下查询来避免。
select t.model, t.date
from doc t
inner join (select model, max(date) as MaxDate from doc group by model)
tm on t.model = tm.model and t.date = tm.MaxDate
回答by Christopher Pelayo
try this:
尝试这个：
SELECT model, date
FROM doc
WHERE date = (SELECT MAX(date)
FROM doc GROUP BY model LIMIT 0, 1)
GROUP BY model
回答by Iman Sedighi
Using max(date) didn't solve my problem as there is no assurance that other columns will be from the same row as the max(date) is. Instead of that this one solved my problem and sorted group by in a correct order and values of other columns are from the same row as the max date is:
使用 max(date) 并没有解决我的问题，因为不能保证其他列与 max(date) 来自同一行。而不是这个解决了我的问题并以正确的顺序对组进行了排序，其他列的值与最大日期来自同一行：
SELECT model, date
FROM (SELECT * FROM doc ORDER BY date DESC) as sortedTable
GROUP BY model
回答by Aldarien
This should work:
这应该有效：
SELECT model, date FROM doc GROUP BY model ORDER BY date DESC
It just sort the dates from last to first and by grouping it only grabs the first one.
它只是从最后到第一个对日期进行排序，并且通过分组它只抓取第一个。
回答by Vasekdvor
And why not to use this ?
为什么不使用这个？
SELECT model, date FROM doc ORDER BY date DESC LIMIT 1
