SQL中MAX()与GROUP BY联用问题：无法关联FileName与File字段

数据库结构 #

+---------------------+------------+--------+------+--+
| Date                | FileName   | Type   | File |  |
+---------------------+------------+--------+------+--+
| 2023-06-15 08:03:04 | InputParam | Param  | .... |  |
+---------------------+------------+--------+------+--+
| 2023-06-15 09:15:34 | InputParam | Param  | .... |  |
+---------------------+------------+--------+------+--+
| 2023-06-16 01:05:07 | OutputSet  | Set    | .... |  |
+---------------------+------------+--------+------+--+
| 2023-06-16 11:22:04 | ConnConfig | Config | .... |  |
+---------------------+------------+--------+------+--+
| 2023-06-16 17:33:00 | ConnConfig | Config | .... |  |
+---------------------+------------+--------+------+--+
| 2023-06-17 06:06:28 | ConnConfig | Config | .... |  |
+---------------------+------------+--------+------+--+

需求 #

生成包含所有列的表格，仅展示每个FileName对应的最新（Date最大）记录，期望结果：

+---------------------+------------+--------+------+--+
| Date                | FileName   | Type   | File |  |
+---------------------+------------+--------+------+--+
| 2023-06-15 09:15:34 | InputParam | Param  | .... |  |
+---------------------+------------+--------+------+--+
| 2023-06-16 01:05:07 | OutputSet  | Set    | .... |  |
+---------------------+------------+--------+------+--+
| 2023-06-17 06:06:28 | ConnConfig | Config | .... |  |
+---------------------+------------+--------+------+--+

尝试的SQL及问题 #

尝试使用以下语句，但无法保证Type、File与最新Date记录的关联：

SELECT MAX(Date) As Date, FileName, Type, File
FROM myTable
WHERE File IS NOT NULL
GROUP BY FileName;

问题原因：GROUP BY FileName仅对FileName分组，MAX(Date)能得到该组的最大日期，但Type和File属于非聚合列，数据库会随机选取组内某条记录的对应值，无法确保和最新日期的记录匹配，尤其File是BLOB类型时更易出现错误匹配。

解决方案 #

方法1：使用窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数，按FileName分组并按Date降序排序，取每组第一条记录：

SELECT Date, FileName, Type, File
FROM (
    SELECT 
        Date, 
        FileName, 
        Type, 
        File,
        ROW_NUMBER() OVER (PARTITION BY FileName ORDER BY Date DESC) AS rn
    FROM myTable
    WHERE File IS NOT NULL
) t
WHERE rn = 1;

解释：

• PARTITION BY FileName：将数据按FileName分成多个组
• ORDER BY Date DESC：每个组内按日期倒序排列，最新的记录排在最前面
• ROW_NUMBER()：给每个组内的记录编号，最新记录编号为1
• 外层查询筛选出编号为1的记录，即为每个FileName的最新完整记录

方法2：子查询匹配最大日期（兼容旧版本数据库） #

先获取每个FileName的最大日期，再关联原表获取对应完整记录：

SELECT t1.Date, t1.FileName, t1.Type, t1.File
FROM myTable t1
INNER JOIN (
    SELECT FileName, MAX(Date) AS MaxDate
    FROM myTable
    WHERE File IS NOT NULL
    GROUP BY FileName
) t2 ON t1.FileName = t2.FileName AND t1.Date = t2.MaxDate
WHERE t1.File IS NOT NULL;

解释：

• 子查询t2先得到每个FileName对应的最大日期
• 主查询通过FileName和Date关联原表t1，获取该日期对应的完整记录
• 若同一FileName存在多条相同最大日期的记录，会返回所有这些记录；若需仅返回一条，可结合DISTINCT或其他逻辑处理

内容的提问来源于stack exchange，提问作者BCas