SQL双表查询：获取Table A符合日期条件或无匹配名的最新数据

需求说明 #

现有Table A和Table B两张表，需针对每个name执行以下逻辑：仅当Table A中该name的最新数据日期晚于Table B中该name的最新日期，或该name不存在于Table B时，获取Table A中该name的最新数据。

原SQL语句（未得到预期结果） #

SELECT t1.* FROM table_a t1 
WHERE t1.date > (SELECT MAX(t2.date) 
FROM table_b t2 
WHERE t1.name = t2.name) 
ORDER BY t1.date DESC LIMIT 1

数据表内容 #

Table A 数据 #

Table B 数据 #

预期结果 #

原SQL问题分析 #

1. 未处理name不存在于Table B的场景：当name在Table B中无匹配时，子查询返回NULL，而t1.date > NULL的逻辑判断结果为UNKNOWN，无法选中该行，导致Bill的数据无法被取出。
2. 结果行数限制错误：末尾的LIMIT 1强制只返回一行，但需求是返回所有符合条件的name的最新数据。
3. 未筛选每个name的最新数据：原SQL仅筛选出Table A中日期大于对应Table B最大日期的行，但没有确保取的是每个name的最新那条记录。

正确SQL实现 #

WITH a_latest AS (
    -- 获取Table A中每个name的最新数据
    SELECT *
    FROM table_a t1
    WHERE NOT EXISTS (
        SELECT 1 FROM table_a t2
        WHERE t2.name = t1.name AND t2.date > t1.date
    )
),
b_latest AS (
    -- 计算Table B中每个name的最新日期
    SELECT name, MAX(date) AS max_date
    FROM table_b
    GROUP BY name
)
-- 筛选符合条件的记录
SELECT a_latest.*
FROM a_latest
LEFT JOIN b_latest ON a_latest.name = b_latest.name
WHERE b_latest.max_date IS NULL -- name不存在于Table B
   OR a_latest.date > b_latest.max_date; -- Table A最新日期晚于Table B

逻辑说明 #

1. a_latest CTE：通过排除同name下日期更大的记录，得到每个name在Table A中的最新数据。
2. b_latest CTE：按name分组聚合，得到每个name在Table B中的最新日期。
3. 主查询：左连接两个CTE，筛选出两种符合需求的场景，最终得到预期结果。

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