如何修正SQL语句实现跨表填充目标表NULL值？

数据表结构 #

table_a #

name    year    var
---------------------
john    2010    a
john    2011    a
john    2012    c
alex    2020    b
alex    2021    c
tim     2015    NULL
tim     2016    NULL
joe     2010    NULL
joe     2011    NULL
jessica 2000    NULL
jessica 2001    NULL

table_b #

name    year    var
---------------------
sara    2001    a
sara    2002    b
tim     2005    c
tim     2006    d
tim     2021    f
jessica 2020    z

需求说明 #

• 提取table_a中var字段为NULL的记录对应的name
• 检查这些name是否存在于table_b中
• 若存在，进一步查看table_b中该name是否有年份早于table_a对应记录年份的行
• 若满足条件，用table_b中最接近table_a该name最早年份的var值替换NULL，否则保持原样

尝试的SQL语句 #

WITH min_year AS (
    SELECT name, MIN(year) as min_year
    FROM table_a
    GROUP BY name
  ),
  b_filtered AS (
    SELECT b.name, MAX(b.year) as year, b.var
    FROM table_b b
    INNER JOIN min_year m ON b.name = m.name AND b.year < m.min_year
    GROUP BY b.name
  )
  SELECT a.name, a.year, 
    CASE 
      WHEN a.var IS NULL AND b.name IS NOT NULL THEN b.var
      ELSE a.var
    END as var_mod
  FROM table_a a
  LEFT JOIN b_filtered b
  ON a.name = b.name;

当前错误输出 #

name    year    var_mod
-------------------------
john    2010    a
john    2011    a
john    2012    c
alex    2020    b
alex    2021    c
tim     2015    NULL
tim     2016    NULL
joe     2010    NULL
joe     2011    NULL
jessica 2000    NULL
jessica 2001    NULL

期望正确输出 #

name    year    var_mod
-------------------------
john    2010    a
john    2011    a
john    2012    c
alex    2020    b
alex    2021    c
tim     2015    d
tim     2016    d
joe     2010    NULL
joe     2011    NULL
jessica 2000    NULL
jessica 2001    NULL

修正方案及解释 #

原SQL的核心问题在于b_filtered中，SELECT b.name, MAX(b.year) as year, b.var的写法不符合聚合规则：当按name分组时，b.var没有被聚合函数处理，在多数SQL数据库的严格模式下会直接报错，即使允许执行，也无法保证拿到的var是对应MAX(b.year)那条记录的值。

以下是修正后的SQL：

WITH a_target_min_year AS (
    -- 仅针对table_a中var为NULL的name，计算其最早年份
    SELECT name, MIN(year) AS min_a_year
    FROM table_a
    WHERE var IS NULL
    GROUP BY name
),
b_matching_records AS (
    SELECT 
        b.name,
        b.var,
        -- 按年份倒序排序，标记每个name最接近min_a_year的候选记录
        ROW_NUMBER() OVER (PARTITION BY b.name ORDER BY b.year DESC) AS rn
    FROM table_b b
    INNER JOIN a_target_min_year a 
        ON b.name = a.name 
        AND b.year < a.min_a_year
)
SELECT 
    a.name,
    a.year,
    CASE 
        WHEN a.var IS NULL THEN COALESCE(b.var, a.var)
        ELSE a.var
    END AS var_mod
FROM table_a a
LEFT JOIN b_matching_records b 
    ON a.name = b.name 
    AND b.rn = 1;

关键修正点： #

1. 缩小CTE范围：a_target_min_year仅计算table_a中var为NULL的name的最早年份，减少不必要的计算。
2. 用窗口函数精准匹配记录：b_matching_records通过ROW_NUMBER()按name分组、年份倒序排序，确保rn=1的记录是每个name在table_b中小于其table_a最早年份的最新（最接近）记录，保证拿到正确的var值。
3. 简化替换逻辑：用COALESCE简化NULL值替换的判断逻辑，代码更简洁。

内容的提问来源于stack exchange，提问作者Uk rain troll