SQL连接后满足日期条件并保留my_table1所有行的实现问题

需求说明 #

• 提取my_table2中每个name对应的**最新日期(var3)**的行
• 将该行与my_table1做关联，要求关联后my_table2.var3 > my_table1.var1
• 不满足条件（包括my_table2无对应name、var3不大于var1）的my_table2字段填充为NA
• 最终结果行数必须与my_table1完全一致

测试数据建表语句 #

CREATE TABLE my_table1 (
    name VARCHAR(50),
    var1 DATE,
    var2 INT
);

INSERT INTO my_table1 (name, var1, var2) VALUES
('john', '2010-01-01', 94),
('john', '2010-01-04', 106),
('john', '2015-01-01', 99),
('alex', '2010-01-01', 96),
('alex', '2018-01-01', 96),
('sara', '2005-01-01', 94),
('sara', '2006-01-01', 90),
('tim',  '1999-01-01', 101);

CREATE TABLE my_table2 (
    name VARCHAR(50),
    var3 DATE,
    var4 CHAR(1)
);

INSERT INTO my_table2 (name, var3, var4) VALUES
('john', '2001-01-01', 'a'),
('john', '2002-01-01', 'b'),
('alex', '2021-01-01', 'c'),
('alex', '2022-01-01', 'd'),
('sara', '1999-01-01', 'e'),
('sara', '2023-01-01', 'f');

尝试的两种错误写法 #

写法一：内连接（缺失John、Tim的NA行） #

SELECT t1.*, t2.*
FROM my_table1 t1
JOIN (
    SELECT name, var3, var4
    FROM (
        SELECT name, var3, var4,
            ROW_NUMBER() OVER (PARTITION BY name ORDER BY var3 DESC) as rn
        FROM my_table2
    ) tmp
    WHERE rn = 1
) t2
ON t1.name = t2.name
WHERE t1.var1 < t2.var3;

写法二：带COALESCE的左连接（缺失John的NA行） #

SELECT t1.*, COALESCE(t2.name, 'NA') as name, t2.var3, t2.var4
FROM my_table1 t1
LEFT JOIN (
    SELECT name, var3, var4
    FROM (
        SELECT name, var3, var4,
            ROW_NUMBER() OVER (PARTITION BY name ORDER BY var3 DESC) as rn
        FROM my_table2
    ) tmp
    WHERE rn = 1
) t2
ON t1.name = t2.name
WHERE t1.var1 < COALESCE(t2.var3, '9999-12-31');

正确实现方式 #

思路解析 #

1. 先通过窗口函数提取my_table2每个name的最新行，避免重复计算
2. 使用左连接保证my_table1的所有行都保留
3. 将var3 > var1的条件放在ON子句中，而非WHERE（否则会过滤掉左连接生成的NULL行）
4. 对my_table2的字段用COALESCE填充NA

最终SQL代码 #

SELECT 
    t1.*,
    COALESCE(t2.name, 'NA') AS t2_name,
    COALESCE(t2.var3::TEXT, 'NA') AS var3,
    COALESCE(t2.var4, 'NA') AS var4
FROM my_table1 t1
LEFT JOIN (
    -- 预取my_table2每个name的最新行
    SELECT name, var3, var4
    FROM (
        SELECT 
            name, var3, var4,
            ROW_NUMBER() OVER (PARTITION BY name ORDER BY var3 DESC) AS rn
        FROM my_table2
    ) tmp
    WHERE rn = 1
) t2 
ON t1.name = t2.name AND t2.var3 > t1.var1;

结果说明 #

• 所有my_table1的行都会被保留，共8行
• John的所有行：my_table2最新var3是2002-01-01，小于John的所有var1，因此t2字段全为NA
• Tim的行：my_table2无对应name，t2字段全为NA
• Alex的所有行：my_table2最新var3是2022-01-01，大于Alex的两个var1，因此正常显示t2字段
• Sara的行：2005、2006的var1都小于2023-01-01，因此正常显示t2字段

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