多表相同行比对与更新：修复end_date字段为空的SQL问题

表结构与测试数据 #

CREATE TABLE old_table (
    name1 VARCHAR(50),
    name2 VARCHAR(50),
    origin_date DATE,
    var1 VARCHAR(50),
    today DATE
);

INSERT INTO old_table (name1, name2, origin_date, var1, today) VALUES
('red_1', 'red', '2010-01-01', 'aaa', '2020-01-01'),
('red_2', 'red', '2011-01-01', 'bbb', '2020-01-01'),
('blue_1', 'blue', '2005-01-01', 'ccc', '2020-01-01'),
('green_1', 'green', '2005-01-01', 'ddd', '2020-01-01');

CREATE TABLE new_table (
    name1 VARCHAR(50),
    name2 VARCHAR(50),
    origin_date DATE,
    var1 VARCHAR(50),
    today DATE
);

INSERT INTO new_table (name1, name2, origin_date, var1, today) VALUES
('purple_1', 'purple', '2001-01-01', 'fff', '2020-01-02'),
('pink_1', 'pink', '2002-01-01', 'ggg', '2020-01-02'),
('red_1', 'red', '2010-01-01', 'aaa', '2020-01-02');

需求说明 #

• 基于name1关联两张表，生成status（取值active或inactive）和end_date（取值new_table的today或NULL）
• 结果需包含两张表的唯一行，区分“消亡”“存活”“新增”数据
• 存活行：status='active'且end_date=NULL；未存活行：status='inactive'且end_date=new_table.today

原SQL问题分析 #

原SQL中，LEFT JOIN部分的end_date直接取new_table.today，但当old_table的行在new_table中不存在时，new_table.today为NULL，导致未存活行的end_date不符合预期。需要先获取new_table的统一日期值，用于赋值给未存活行的end_date。

修复后的SQL #

WITH new_date AS (
    -- 获取new_table的today日期（因所有行日期一致，取任意一行即可）
    SELECT DISTINCT today AS new_today FROM new_table
),
combined AS (
    SELECT 
        o.name1, 
        o.name2, 
        o.origin_date, 
        o.var1, 
        -- 未存活行用new_table的today，存活行设为NULL
        CASE WHEN n.name1 IS NULL THEN nd.new_today ELSE NULL END AS end_date,
        CASE WHEN n.name1 IS NOT NULL THEN 'active' ELSE 'inactive' END AS status
    FROM old_table o
    CROSS JOIN new_date nd
    LEFT JOIN new_table n ON o.name1 = n.name1
    UNION ALL
    -- 新增行直接设置active和NULL
    SELECT 
        n.name1, 
        n.name2, 
        n.origin_date, 
        n.var1, 
        NULL AS end_date, 
        'active' AS status
    FROM new_table n
    WHERE n.name1 NOT IN (SELECT name1 FROM old_table)
)
SELECT * FROM combined
ORDER BY name1;

验证结果 #

执行后将得到符合预期的结果：

name1  name2 origin_date var1  status   end_date
    red_1    red  2010-01-01  aaa  active       NULL
    red_2    red  2011-01-01  bbb inactive 2020-01-02
   blue_1   blue  2005-01-01  ccc inactive 2020-01-02
  green_1  green  2005-01-01  ddd inactive 2020-01-02
purple_1 purple  2001-01-01  fff  active       NULL
  pink_1   pink  2002-01-01  ggg  active       NULL

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