MySQL中从UNION结果按MAX(dateEmailed)获取关联字段正确的最新记录

问题根源 #

你之前的查询用GROUP BY familyId配合MAX(dateEmailed)只能拿到每个家庭的最新日期，但其他字段（比如amount、statementurl）会被MySQL随机选取分组内的某条记录值，这是因为MySQL允许非标准的GROUP BY语法——不对非分组字段使用聚合函数时，会返回分组内的任意一条记录，而非对应最新日期的那条。

正确实现方案 #

方案1：子查询取最新日期后关联（兼容所有MySQL版本） #

先通过子查询算出每个familyId的最新发送日期，再把合并后的全量记录和这个结果关联，筛选出匹配的完整记录：

SELECT t.*
FROM (
    -- 合并两个表的所有字段
    SELECT familyId, dateEmailed, amount, statementurl -- 替换成你需要的所有字段
    FROM FamilyStatement
    UNION ALL -- 两表无重复时用UNION ALL更高效，有重复则用UNION去重
    SELECT familyId, dateEmailed, amount, statementurl
    FROM AccountStatement
) t
INNER JOIN (
    -- 计算每个familyId的最新dateEmailed
    SELECT familyId, MAX(dateEmailed) AS latest_date
    FROM (
        SELECT familyId, dateEmailed
        FROM FamilyStatement
        UNION ALL
        SELECT familyId, dateEmailed
        FROM AccountStatement
    ) temp
    GROUP BY familyId
) latest 
ON t.familyId = latest.familyId AND t.dateEmailed = latest.latest_date;

方案2：窗口函数ROW_NUMBER()（MySQL 8.0+适用） #

如果你的MySQL版本是8.0及以上，用窗口函数更简洁：按familyId分组，给每组的记录按dateEmailed倒序排号，取排号为1的那条（就是最新的记录）：

SELECT familyId, dateEmailed, amount, statementurl
FROM (
    SELECT 
        *,
        -- 按familyId分组，dateEmailed降序排序，每条记录获得一个序号
        ROW_NUMBER() OVER(PARTITION BY familyId ORDER BY dateEmailed DESC) AS rn
    FROM (
        SELECT familyId, dateEmailed, amount, statementurl
        FROM FamilyStatement
        UNION ALL
        SELECT familyId, dateEmailed, amount, statementurl
        FROM AccountStatement
    ) t
) ranked
WHERE rn = 1;

方案3：自连接筛选最新记录（兼容所有MySQL版本） #

通过自连接，找出每个familyId中不存在比当前记录日期更晚的记录——那这条就是最新的：

SELECT t1.*
FROM (
    SELECT familyId, dateEmailed, amount, statementurl
    FROM FamilyStatement
    UNION ALL
    SELECT familyId, dateEmailed, amount, statementurl
    FROM AccountStatement
) t1
LEFT JOIN (
    SELECT familyId, dateEmailed
    FROM FamilyStatement
    UNION ALL
    SELECT familyId, dateEmailed
    FROM AccountStatement
) t2 
ON t1.familyId = t2.familyId AND t2.dateEmailed > t1.dateEmailed
-- 没有比当前记录日期更晚的，说明当前是最新的
WHERE t2.familyId IS NULL;

小提示 #

• 优先用UNION ALL代替UNION，因为UNION会自动去重，性能比UNION ALL差，除非你确实需要去重。
• 确保dateEmailed是DATE/DATETIME类型，避免用字符串存储日期导致排序错误。

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