从分组的 MySQL 数据中获取最新日期 [英] Get the latest date from grouped MySQL data

问题描述

我的数据库中有以下数据:

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|

我试过了:

SELECT model, date 
FROM doc
WHERE date ........????? //what is the next?
GROUP BY model

推荐答案

您是否正在寻找每个模型的最大日期?

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 ---]

对于那些想要显示每个模型组中与最新日期匹配的每条记录的详细信息的人(不是 OP 中要求的汇总数据):

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):

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 及更新版本支持 OVER 子句，对于较大的数据集产生相同的结果会更快一些.

MySQL 8.0 and newer supports the OVER clause, producing the same results a bit faster for larger data sets.

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;

这篇关于从分组的 MySQL 数据中获取最新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！