如何用SQL合并含Null值的重复客户记录，保留最新有效信息？

假设你的客户表名为customers，包含以下字段：customer_id（用于唯一标识同一客户）、create_date（记录创建日期）、last_name、city、county、address。以下是两种实现需求的SQL方案：

方案一：窗口函数+聚合函数结合 #

WITH ranked_customers AS (
    SELECT 
        customer_id,
        last_name,
        city,
        county,
        address,
        create_date,
        -- 按客户分组，给记录按创建日期倒序排名，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY create_date DESC) AS rn
    FROM customers
)
SELECT 
    customer_id,
    -- 优先取最新记录的last_name，为空则取该客户所有记录的非空last_name
    COALESCE(MAX(CASE WHEN rn = 1 THEN last_name END), MAX(last_name)) AS last_name,
    -- 同理处理city字段
    COALESCE(MAX(CASE WHEN rn = 1 THEN city END), MAX(city)) AS city,
    -- county字段逻辑一致
    COALESCE(MAX(CASE WHEN rn = 1 THEN county END), MAX(county)) AS county,
    -- address字段逻辑一致
    COALESCE(MAX(CASE WHEN rn = 1 THEN address END), MAX(address)) AS address
FROM ranked_customers
GROUP BY customer_id;

方案二：LATERAL JOIN获取最新记录再合并 #

SELECT 
    c.customer_id,
    COALESCE(latest.last_name, MAX(c.last_name)) AS last_name,
    COALESCE(latest.city, MAX(c.city)) AS city,
    COALESCE(latest.county, MAX(c.county)) AS county,
    COALESCE(latest.address, MAX(c.address)) AS address
FROM customers c
-- 关联每个客户的最新记录
JOIN LATERAL (
    SELECT last_name, city, county, address
    FROM customers
    WHERE customer_id = c.customer_id
    ORDER BY create_date DESC
    LIMIT 1
) latest ON true
GROUP BY c.customer_id, latest.last_name, latest.city, latest.county, latest.address;

关键说明 #

1. 字段优先级逻辑：通过COALESCE函数实现「优先取最新记录值，为空则取历史非空值」的逻辑，MAX函数会自动忽略NULL值，取对应字段的非空值。
2. 客户唯一标识：请确保customer_id能准确区分同一客户；如果没有这类字段，可通过LOWER(last_name)等方式统一姓名格式后作为分组依据（需注意姓名重复的特殊情况）。
3. 日期格式：如果create_date是字符串类型，需先转换为日期格式再排序，例如PostgreSQL中用TO_DATE(create_date, 'DD-MM-YYYY')，MySQL中用STR_TO_DATE(create_date, '%d-%m-%Y')。

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