SQL多表关联对两个不同日期列排名的代码修复问题

问题原因分析 #

• 字段拼写错误：原代码中将ID字段误写为I’d，会直接导致查询异常或者结果不符合预期。
• 排名逻辑错误：你需要按ID分组取最新的日期，所以PARTITION BY仅需指定ID字段，不需要额外加日期字段；排序规则也应该按照日期降序排列，而非原代码中的ID降序，原逻辑下每个不同的日期都会被划分为独立分区，根本达不到分组取最新日期的效果。
• 笛卡尔积问题：t2、t3同ID各有2条记录，直接关联后会生成2*2=4条临时数据，即使排名逻辑修正，也会出现多条数据匹配的问题，无法得到你想要的单条结果。

修复方案 #

如果仅需要获取每个ID对应的最新开始日期和最新结束日期，用聚合函数的写法更简单高效：

SELECT 
    t1.name,
    MAX(t2.start_date) AS latest_start_date,
    MAX(t3.end_date) AS latest_end_date
FROM t1
LEFT JOIN t2 ON t1.ID = t2.ID
LEFT JOIN t3 ON t1.ID = t3.ID
GROUP BY t1.ID, t1.name

如果你需要保留开窗排名的写法，可以先分别对t2、t3做过滤，取出每个ID对应的最新日期记录，再和t1关联：

WITH t2_latest AS (
    SELECT 
        ID,
        start_date,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY start_date DESC) AS rnk
    FROM t2
),
t3_latest AS (
    SELECT 
        ID,
        end_date,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY end_date DESC) AS rnk
    FROM t3
)
SELECT 
    t1.name,
    t2.start_date,
    t3.end_date
FROM t1
LEFT JOIN t2_latest t2 ON t1.ID = t2.ID AND t2.rnk = 1
LEFT JOIN t3_latest t3 ON t1.ID = t3.ID AND t3.rnk = 1

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