查询同文件夹下重复文件的最新修改记录

需求 #

检索同文件夹下重复文件的最新修改记录。

原始数据表 #

col1        col2        col3
filepath1   filename1   modified date1
filepath1   filename1   modified date1
filepath1   filename1   modified date2  <- 该文件夹下此文件的最新修改记录
filepath1   filename2   modified date3
filepath1   filename3   modified date4

filepath2   filename2   modified date3  <- 该文件夹下此文件的最新修改记录
filepath2   filename2   modified date1
filepath2   filename2   modified date2
filepath2   filename3   modified date1

filepath3   filename3   modified date1
filepath3   filename1   modified date1
filepath3   filename2   modified date1
filepath3   filename3   modified date2  <- 该文件夹下此文件的最新修改记录
filepath3   filename4   modified date1

期望输出 #

filepath1   filename1   modified date2
filepath2   filename2   modified date3
filepath3   filename3   modified date2

我尝试的SQL语句（不确定是否正确） #

string duptbl = "SELECT * FROM mytable
INNER JOIN(
SELECT col1, col2, col3
FROM mytable
GROUP BY fname
HAVING COUNT(id) >1
)temp ON mytable.fname = temp.fname;";

问题分析与修正后的SQL #

你写的SQL存在几个明显问题：

1. 字段名不匹配：原始表字段是col1（文件路径）、col2（文件名）、col3（修改日期），但你用了未定义的fname字段；
2. 分组逻辑错误：要筛选同文件夹下的重复文件，需按col1（文件夹）+col2（文件名）分组，而非单个字段；
3. 未筛选最新记录：语句仅找出重复条目，但未提取每组的最新修改日期。

以下是两种可行的正确写法：

方式一：窗口函数（适用于MySQL8+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

SELECT col1, col2, col3
FROM (
    SELECT 
        col1, 
        col2, 
        col3,
        ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS rn
    FROM mytable
) t
WHERE rn = 1
AND EXISTS (
    SELECT 1 
    FROM mytable t2 
    WHERE t2.col1 = t.col1 AND t2.col2 = t.col2 
    GROUP BY t2.col1, t2.col2 
    HAVING COUNT(*) > 1
);

• PARTITION BY col1, col2：按文件夹+文件名分组
• ORDER BY col3 DESC：每组内按修改日期降序排列，最新记录排第一位
• rn = 1：提取每组的第一条（最新记录）
• EXISTS子句：仅保留存在重复的文件记录

方式二：分组聚合（兼容旧版数据库） #

SELECT t1.col1, t1.col2, t1.col3
FROM mytable t1
INNER JOIN (
    SELECT col1, col2, MAX(col3) AS latest_modified
    FROM mytable
    GROUP BY col1, col2
    HAVING COUNT(*) > 1
) t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.col3 = t2.latest_modified;

• 子查询先按文件夹+文件名分组，找出每组的最新修改日期，同时筛选出重复记录（COUNT(*) >1）
• 主查询关联原表，获取对应最新日期的完整记录

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