SQL查询：按Admission Letter Template ID获取最新时间的完整记录

问题原因 #

直接分组查询时加入Admission_Letter_Log_ID会出现重复模板ID的结果，因为Admission_Letter_Log_ID是唯一标识，每个ID都会单独成组，无法实现按模板ID去重的需求。

解决方案 #

方法一：窗口函数ROW_NUMBER()（推荐）

利用窗口函数按模板ID分组，对每组内的记录按生成时间倒序排序，仅保留排名第一的最新记录：

SELECT Admission_Letter_Log_ID,
       `Admission Letter Template ID`,
       Letter_Generation_Datetime,
       -- 替换为你需要的其他字段
       Other_Fields
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY `Admission Letter Template ID` 
               ORDER BY Letter_Generation_Datetime DESC
           ) AS record_rank
    FROM CoC_Admission_Letter_Log
) ranked_records
WHERE record_rank = 1;

• PARTITION BY：按模板ID将数据分组
• ORDER BY Letter_Generation_Datetime DESC：每组内按生成时间从新到旧排序
• 外层筛选record_rank = 1：仅保留每组的第一条（最新）记录

方法二：关联子查询（兼容低版本数据库）

先通过子查询获取每个模板ID对应的最新生成时间，再关联原表匹配对应记录：

SELECT l.Admission_Letter_Log_ID,
       l.`Admission Letter Template ID`,
       l.Letter_Generation_Datetime,
       l.Other_Fields
FROM CoC_Admission_Letter_Log l
INNER JOIN (
    SELECT `Admission Letter Template ID`,
           MAX(Letter_Generation_Datetime) AS latest_datetime
    FROM CoC_Admission_Letter_Log
    GROUP BY `Admission Letter Template ID`
) latest_times 
    ON l.`Admission Letter Template ID` = latest_times.`Admission Letter Template ID`
    AND l.Letter_Generation_Datetime = latest_times.latest_datetime;

注意：如果同一模板ID在同一时间生成多条记录，此方法会返回多条结果。若需唯一记录，可在子查询中进一步结合MAX(Admission_Letter_Log_ID)筛选：

SELECT l.Admission_Letter_Log_ID,
       l.`Admission Letter Template ID`,
       l.Letter_Generation_Datetime,
       l.Other_Fields
FROM CoC_Admission_Letter_Log l
INNER JOIN (
    SELECT `Admission Letter Template ID`,
           MAX(Admission_Letter_Log_ID) AS latest_log_id
    FROM CoC_Admission_Letter_Log
    WHERE (`Admission Letter Template ID`, Letter_Generation_Datetime) IN (
        SELECT `Admission Letter Template ID`,
               MAX(Letter_Generation_Datetime) AS latest_datetime
        FROM CoC_Admission_Letter_Log
        GROUP BY `Admission Letter Template ID`
    )
    GROUP BY `Admission Letter Template ID`
) latest_records 
    ON l.Admission_Letter_Log_ID = latest_records.latest_log_id;

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