多数据集关联后保留最新日期行且不丢失空值行的SQL/R方案

需求说明 #

需将Table1、Table2、Table3通过Common ID关联，满足以下要求：

• 保留Table1中所有Common ID（包括对应Table2/Table3为空值的行）
• 每个Common ID仅保留最新的Last Enquiry Date（来自Table2）和最新的Last Completion Date（来自Table3）
• 此前使用Inner Join丢失空值行，Full Outer Join导致日期筛选失效，需SQL优先的可行方案

表结构 #

Table 1（主表，包含所有需保留的Common ID） #

Table 2（存储查询日期记录） #

Table 3（存储完成日期记录） #

SQL解决方案 #

核心思路：先对Table2和Table3分别按Common ID聚合提取最新日期，再用LEFT JOIN关联主表Table1，确保保留所有主表ID，同时空值行不会丢失。

SELECT
    t1."Common ID",
    t1."District",
    t1."Floor",
    -- 转换回字符串格式保持与原数据一致，若无需转换可直接用聚合后的日期字段
    TO_CHAR(t2_latest."Last Enquiry Date", 'DD/MM/YYYY') AS "Last Enquiry Date",
    TO_CHAR(t3_latest."Last Completion Date", 'DD/MM/YYYY') AS "Last Completion Date"
FROM
    "Table1" t1
LEFT JOIN (
    -- 提取Table2中每个Common ID的最新查询日期
    SELECT
        "Common ID",
        MAX(TO_DATE("Last Enquiry Date", 'DD/MM/YYYY')) AS "Last Enquiry Date"
    FROM
        "Table2"
    GROUP BY
        "Common ID"
) t2_latest ON t1."Common ID" = t2_latest."Common ID"
LEFT JOIN (
    -- 提取Table3中每个Common ID的最新完成日期
    SELECT
        "Common ID",
        MAX(TO_DATE("Last Completion Date", 'DD/MM/YYYY')) AS "Last Completion Date"
    FROM
        "Table3"
    GROUP BY
        "Common ID"
) t3_latest ON t1."Common ID" = t3_latest."Common ID";

关键说明 #

1. 聚合子查询：通过GROUP BY+MAX()获取每个Common ID的最新日期，TO_DATE()将字符串日期转为可比较的日期类型（若数据库中日期已是日期类型可省略）。
2. LEFT JOIN：以Table1为主表关联聚合后的子表，确保主表所有ID都被保留，即使对应子表无数据或为空值。
3. 空值兼容：MAX()函数自动忽略空值，若某Common ID的所有日期为空，聚合结果仍为空，符合需求。
4. 数据库适配：不同数据库的日期转换函数不同，可按需替换：
   • SQL Server：用CONVERT(DATE, 字段名, 103)替代TO_DATE，CONVERT(VARCHAR, 字段名, 103)替代TO_CHAR
   • MySQL：用STR_TO_DATE(字段名, '%d/%m/%Y')替代TO_DATE，DATE_FORMAT(字段名, '%d/%m/%Y')替代TO_CHAR

理想输出 #

（注：原需求中A002的Last Completion Date标注为29/11/2021，实际该ID最新日期为20/05/2022，SQL逻辑会自动取最大值）

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