如何编写SQL查询Table A全量数据并关联Table B指定类型最新日期？

现有数据表结构及数据 #

Table A #

+---------------+
| ID   NAME     |
+---------------+
| 001    A      |
| 002    B      |
| 003    C      |
+---------------+

Table B #

+------------------------------+
| ID    TYPE       DATE        |
+------------------------------+
| 001    URGE     2024-01-01   |
| 001    UPDATE   2024-01-02   |
| 002    UPDATE   2024-01-03   |
| 001    URGE     2023-12-30   |
+------------------------------+

期望查询结果 #

+-------------------------------------+
| ID    NAME   TYPE       DATE        |
+-------------------------------------+
| 001    A    URGE     2024-01-01     |
| 002    B    -        -              |
| 003    C    -        -              |
+-------------------------------------+

需求说明 #

查询Table A中的所有数据，关联Table B中TYPE = 'URGE'的记录，并输出对应ID下最新的DATE；若无匹配记录，则DATE列输出NULL（结果中用-替代NULL仅为展示效果，实际SQL可按需调整）。

解决方案 #

方法一：窗口函数实现（推荐，逻辑清晰） #

SELECT 
    a.ID,
    a.NAME,
    COALESCE(b.TYPE, '-') AS TYPE,
    COALESCE(b.DATE, '-') AS DATE
FROM Table_A a
LEFT JOIN (
    SELECT 
        ID,
        TYPE,
        DATE,
        -- 按ID分组，日期倒序取最新的URGE记录
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE DESC) AS rn
    FROM Table_B
    WHERE TYPE = 'URGE'
) b ON a.ID = b.ID AND b.rn = 1;

方法二：子查询匹配最大日期 #

SELECT 
    a.ID,
    a.NAME,
    COALESCE(b.TYPE, '-') AS TYPE,
    COALESCE(b.DATE, '-') AS DATE
FROM Table_A a
LEFT JOIN Table_B b 
    ON a.ID = b.ID 
    AND b.TYPE = 'URGE'
    -- 匹配当前ID下最新的URGE日期
    AND b.DATE = (
        SELECT MAX(DATE) 
        FROM Table_B 
        WHERE ID = a.ID AND TYPE = 'URGE'
    );

关键说明 #

• LEFT JOIN确保Table A的所有数据都会被返回，不受Table B匹配结果影响
• COALESCE用于将NULL替换为展示用的-，如果不需要替换，直接去掉该函数保留原始NULL即可
• 两种方法都能精准筛选出每个ID对应的最新URGE记录，可根据使用的SQL方言选择适配方案

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