求助：按Term分组获取对应最新UploadDate的SQL语句修正

你的问题根源在于子查询没有和外层的Term关联，导致它计算的是所有符合条件记录中的全局最大UploadDate，而非每个Term组内的最大值。下面提供几种可行的修正方案：

方案1：使用窗口函数（推荐，现代数据库通用） #

利用ROW_NUMBER()窗口函数给每个Term组内的记录按UploadDate降序编号，取编号为1的记录就是每组的最新数据：

SELECT Inst, Term, Year, FreezeDate, UploadDate, RecordCount, ErrorCount, FileName, System
FROM (
    SELECT 
        *,
        -- 按Term分组，每组内按UploadDate倒序排，最新的记录行号为1
        ROW_NUMBER() OVER (PARTITION BY Term ORDER BY UploadDate DESC) AS row_num
    FROM table 
    WHERE System = 'a' AND Year = '2017' AND Inst = '123' AND Term IN ('1', '4')
) grouped_data
WHERE row_num = 1;

这种方法简洁直观，支持MySQL 8.0+、PostgreSQL、SQL Server等绝大多数现代数据库。

方案2：关联子查询（兼容性更好） #

让子查询和外层表的Term字段关联，这样每个Term都会单独计算自己组内的最大UploadDate：

SELECT Inst, Term, Year, FreezeDate, UploadDate, RecordCount, ErrorCount, FileName, System
FROM table t_main
WHERE 
    System = 'a' AND Year = '2017' AND Inst = '123' AND Term IN ('1', '4')
    -- 子查询关联外层的Term，计算当前Term组内的最大UploadDate
    AND UploadDate = (
        SELECT MAX(UploadDate) 
        FROM table t_sub
        WHERE t_sub.Term = t_main.Term 
          AND t_sub.System = 'a' AND t_sub.Year = '2017' AND t_sub.Inst = '123'
    );

方案3：GROUP BY + JOIN #

先通过GROUP BY得到每个Term的最大UploadDate，再和原表关联获取完整记录：

SELECT t_main.Inst, t_main.Term, t_main.Year, t_main.FreezeDate, t_main.UploadDate, 
       t_main.RecordCount, t_main.ErrorCount, t_main.FileName, t_main.System
FROM table t_main
JOIN (
    -- 按Term分组，得到每组的最大UploadDate
    SELECT Term, MAX(UploadDate) AS max_upload_date
    FROM table
    WHERE System = 'a' AND Year = '2017' AND Inst = '123' AND Term IN ('1', '4')
    GROUP BY Term
) t_grouped ON t_main.Term = t_grouped.Term AND t_main.UploadDate = t_grouped.max_upload_date
WHERE t_main.System = 'a' AND t_main.Year = '2017' AND t_main.Inst = '123';

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