无公共列的表关联问题：row_number函数关联存在异常

问题背景 #

• 表1结构：id(int)、end_date(datetime)
• 表2结构：start_date(datetime)
• 需求：将表2的start_date与表1每条id记录对应关联
• 原方案缺陷：使用row_number()按日期排序关联时，空日期会打乱排序逻辑，且两张表行数不同时无法正确匹配所有记录

原尝试代码：

select id, end_date, start_date  from (select id, end_date, row_number() over (order by end_date) date_order from table1) b
left join 
(select start_date, row_number() over (order by start_date) date_order from table2) a
on a.date_order = b.date_order

解决方案 #

方案1：按稳定顺序匹配（不依赖日期） #

如果不需要按日期逻辑关联，仅需让表2记录按顺序对应表1的每条记录，可使用主键或默认存储顺序生成行号，避免空日期干扰：

SELECT 
    b.id, 
    b.end_date, 
    a.start_date
FROM (
    SELECT 
        id, 
        end_date, 
        ROW_NUMBER() OVER (ORDER BY id) AS row_num  -- 按主键id排序，顺序稳定不受空日期影响
    FROM table1
) b
LEFT JOIN (
    SELECT 
        start_date, 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num  -- 按表2默认存储顺序生成行号
    FROM table2
) a ON a.row_num = b.row_num;

• 优势：行号生成逻辑稳定，空日期不会打乱匹配顺序；左连接保证表1所有记录保留，表2行数不足时start_date为NULL。

方案2：按日期逻辑关联（单独处理空日期） #

若需按日期匹配，可将空日期与非空日期分组处理，避免空值干扰排序：

WITH table1_with_row AS (
    SELECT 
        id, 
        end_date,
        CASE 
            WHEN end_date IS NULL THEN 'null_group' 
            ELSE 'normal_group' 
        END AS date_group,
        ROW_NUMBER() OVER (
            PARTITION BY CASE WHEN end_date IS NULL THEN 'null_group' ELSE 'normal_group' END
            ORDER BY COALESCE(end_date, '9999-12-31')  -- 空日期排至末尾，可根据需求调整
        ) AS row_num
    FROM table1
),
table2_with_row AS (
    SELECT 
        start_date,
        CASE 
            WHEN start_date IS NULL THEN 'null_group' 
            ELSE 'normal_group' 
        END AS date_group,
        ROW_NUMBER() OVER (
            PARTITION BY CASE WHEN start_date IS NULL THEN 'null_group' ELSE 'normal_group' END
            ORDER BY COALESCE(start_date, '9999-12-31')
        ) AS row_num
    FROM table2
)
SELECT 
    t1.id, 
    t1.end_date, 
    t2.start_date
FROM table1_with_row t1
LEFT JOIN table2_with_row t2 
    ON t1.date_group = t2.date_group 
    AND t1.row_num = t2.row_num;

• 逻辑：将空日期与非空日期分为两组，每组内按日期排序生成行号，同组内记录一一关联，空日期不会干扰正常日期的匹配逻辑。

方案3：保留两张表所有记录（行数不一致时） #

若需同时保留表1和表2的所有记录，使用全连接替代左连接：

SELECT 
    COALESCE(b.id, NULL) AS id, 
    b.end_date, 
    a.start_date
FROM (
    SELECT 
        id, 
        end_date, 
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM table1
) b
FULL JOIN (
    SELECT 
        start_date, 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num
    FROM table2
) a ON a.row_num = b.row_num;

• 效果：两张表的所有记录都会被保留，行数不足的一方对应字段显示为NULL。

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