将右外连接中的空值替换为先前非空匹配的行

右外连接中空值替换为非空匹配行的解决方案

基础概念

右外连接(RIGHT OUTER JOIN)是SQL中的一种连接操作，它会返回右表的所有记录，即使左表中没有匹配的记录。对于左表中没有匹配的行，结果集中对应的列会显示为NULL值。

问题描述

在某些业务场景中，我们希望在右外连接的结果中，当左表没有匹配项时，不是简单地显示NULL，而是使用最近的非空匹配行的值来填充这些NULL值。

解决方案

方法1：使用窗口函数和COALESCE

WITH joined_data AS (
    SELECT 
        r.*,
        l.value AS left_value
    FROM 
        right_table r
    LEFT JOIN 
        left_table l ON r.key = l.key
),
filled_data AS (
    SELECT 
        *,
        LAST_VALUE(left_value) IGNORE NULLS OVER (
            PARTITION BY some_grouping_column 
            ORDER BY some_ordering_column
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS filled_value
    FROM 
        joined_data
)
SELECT 
    key,
    COALESCE(left_value, filled_value) AS final_value
FROM 
    filled_data;

方法2：使用LAG函数查找前一个非空值

WITH joined_data AS (
    SELECT 
        r.*,
        l.value AS left_value
    FROM 
        right_table r
    LEFT JOIN 
        left_table l ON r.key = l.key
    ORDER BY 
        r.some_column
)
SELECT 
    key,
    CASE 
        WHEN left_value IS NOT NULL THEN left_value
        ELSE (
            SELECT l2.value 
            FROM left_table l2 
            JOIN right_table r2 ON l2.key = r2.key
            WHERE r2.some_column < r.some_column
            AND l2.value IS NOT NULL
            ORDER BY r2.some_column DESC
            LIMIT 1
        )
    END AS final_value
FROM 
    joined_data r;

方法3：使用递归CTE (适用于PostgreSQL等支持递归查询的数据库)

WITH RECURSIVE filled_data AS (
    -- 基础查询：获取右外连接结果
    SELECT 
        r.id,
        r.key,
        l.value AS left_value,
        ROW_NUMBER() OVER (ORDER BY r.id) AS rn
    FROM 
        right_table r
    LEFT JOIN 
        left_table l ON r.key = l.key
    
    UNION ALL
    
    -- 递归部分：填充NULL值
    SELECT 
        fd.id,
        fd.key,
        COALESCE(fd.left_value, prev.left_value) AS left_value,
        fd.rn
    FROM 
        filled_data fd
    JOIN 
        filled_data prev ON fd.rn = prev.rn + 1
    WHERE 
        fd.left_value IS NULL
)
SELECT 
    id, key, left_value
FROM 
    filled_data
WHERE 
    left_value IS NOT NULL OR rn = 1;