如何在SQL多表连接中筛选最小visit_date并去除重复记录

方案1：基于现有JOIN逻辑最小改动 #

你只需要调整SELECT字段、增加GROUP BY逻辑即可，修改后代码如下：

SELECT
    a.cust_id,
    a.visit_id,
    -- 取四个表的visit_date的最小值，COALESCE把空值替换为极大日期避免影响最小值计算
    MIN(LEAST(
        COALESCE(a.visit_date, '9999-12-31'),
        COALESCE(b.visit_date, '9999-12-31'),
        COALESCE(c.visit_date, '9999-12-31'),
        COALESCE(d.visit_date, '9999-12-31')
    )) AS visit_date
FROM a
LEFT JOIN b 
    ON a.visit_id = b.visit_id
LEFT JOIN c
    ON a.visit_id = c.visit_id
LEFT JOIN d
    ON a.visit_id = d.visit_id
WHERE a.visit_date IS NOT NULL
OR b.visit_date IS NOT NULL
OR c.visit_date IS NOT NULL
OR d.visit_date IS NOT NULL
-- 按cust_id和visit_id分组去重，同时配合MIN函数取最早日期
GROUP BY a.cust_id, a.visit_id;

方案2：更稳妥的合并后分组方案 #

如果担心多表JOIN可能产生重复行，也可以先把四个表的有效数据合并再分组取最小值，逻辑更简洁：

WITH all_visit_records AS (
    -- 合并四个表的所有有效访问记录
    SELECT cust_id, visit_id, visit_date FROM a WHERE visit_date IS NOT NULL
    UNION ALL
    SELECT cust_id, visit_id, visit_date FROM b WHERE visit_date IS NOT NULL
    UNION ALL
    SELECT cust_id, visit_id, visit_date FROM c WHERE visit_date IS NOT NULL
    UNION ALL
    SELECT cust_id, visit_id, visit_date FROM d WHERE visit_date IS NOT NULL
)
SELECT 
    cust_id,
    visit_id,
    MIN(visit_date) AS visit_date -- 按分组取最早日期
FROM all_visit_records
GROUP BY cust_id, visit_id;

两种方案都可以得到你期望的输出结果，第二种方案适配性更强，也不需要额外处理JOIN带来的空值问题。

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