如何实现SQL中客户起始与结束日期表的匹配关联？

现有两张数据表：

• 表A包含customer_id（客户ID）和start_date（起始日期）字段
• 表B包含customer_id（客户ID）和end_date（结束日期）字段

具体数据如下：
表A

表B

需求：将两表按customer_id匹配关联，实现同客户的start_date与end_date按日期顺序一一对应，无对应end_date的记录留空，得到如下期望输出：

期望输出

由于两表中同一客户的日期均为递增顺序，我们可以通过给每个客户的记录添加行号，再通过customer_id和行号进行关联，同时使用左连接保留表A的所有记录。具体SQL如下：

WITH a_with_row AS (
    SELECT 
        customer_id,
        start_date,
        ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY start_date) AS row_num
    FROM 表A
),
b_with_row AS (
    SELECT 
        customer_id,
        end_date,
        ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY end_date) AS row_num
    FROM 表B
)
SELECT 
    a.customer_id,
    a.start_date,
    b.end_date
FROM a_with_row a
LEFT JOIN b_with_row b 
    ON a.customer_id = b.customer_id 
    AND a.row_num = b.row_num
ORDER BY a.customer_id, a.start_date;

代码说明 #

1. CTE a_with_row：给表A中每个客户的记录按start_date排序，添加行号row_num，标记同客户下的第几条起始记录。
2. CTE b_with_row：同理，给表B中每个客户的记录按end_date排序，添加行号row_num，标记同客户下的第几条结束记录。
3. 左连接关联：通过customer_id和row_num将两个CTE关联，左连接保证表A的所有记录都被保留，当表B中没有对应行号的记录时，end_date会显示为NULL（对应结果中的空值）。
4. 排序：最后按customer_id和start_date排序，确保输出顺序与期望一致。

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