MySQL需要在group by内返回结果集中的特定记录

Question

我需要构建一个 MySQL 查询，在其中我可以根据一个或多个列值将结果分组在一起，然后返回该组中符合某些条件的完整记录。

以下表代表我的数据:

| ID | NAME | Editor | Modified date | Type |
|  1 | doc1 | Smith  | 2012-01-01    | A    |
|  2 | doc2 | Smith  | 2012-03-03    | A    |
|  3 | doc1 | Jones  | 2012-10-10    | A    |
|  4 | doc1 | Scott  | 2011-01-01    | A    |
|  5 | doc2 | Boyd   | 2013-01-01    | A    |
|  6 | doc3 | Smith  | 2010-01-01    | B    |
|  7 | doc4 | Smith  | 2010-03-03    | B    |
|  8 | doc3 | Jones  | 2010-10-10    | B    |
|  9 | doc3 | Scott  | 2009-01-01    | B    |
| 10 | doc4 | Boyd   | 2011-01-01    | B    |

我想返回与任何文档相关的最近修改的记录。

| ID | NAME | Editor | Modified date | Type |
|  3 | doc1 | Jones  | 2012-10-10    | A    |
|  5 | doc2 | Boyd   | 2013-01-01    | A    |
|  8 | doc3 | Jones  | 2010-10-10    | B    |
| 10 | doc4 | Boyd   | 2011-01-01    | B    |

或者最近修改过的与类型A相关的记录

| ID | NAME | Editor | Modified date | Type |
|  3 | doc1 | Jones  | 2012-10-10    | A    |
|  5 | doc2 | Boyd   | 2013-01-01    | A    |

我已经尝试过 GROUP BY 和 HAVING，据我所知，它们不会返回整条记录。

附录:可能我需要将“最近”限定词更改为“在给定日期之前”

Answer 1

使用子查询:

SELECT tbl.* FROM tbl
INNER JOIN (
    SELECT max(Modifed_date) as newestRecordDate, Editor
    FROM tbl
    GROUP BY Editor
) as Newest
on tbl.Modifed_Date = Newest.NewestRecordDate

按类型:

SELECT * FROM tbl
INNER JOIN (
    SELECT max(Modifed_date) as newestRecordDate, Editor
    FROM tbl
    GROUP BY Editor
) as Newest
ON tbl.Modifed_Date = Newest.NewestRecordDate
WHERE Type = 'A'

请注意，可能的问题是修改日期不包括时间，并且每天有不止一次编辑 - 确保此记录也包括时间部分。