SQL多表关联：仅在首次匹配行添加关联值（保留分类信息）

需求回顾 #

将table_1与table_2按REGION和PERIOD字段关联，同一REGION+PERIOD分组的匹配结果中，仅第一行填充table_2的other value，其余行该字段留空；需保留category分类信息，因此不能使用GROUP BY分组操作。

修正后的建表与插入数据SQL #

-- 创建table_1
CREATE TABLE table_1 (
    REGION VARCHAR(50),
    PERIOD VARCHAR(50),
    some_value DECIMAL(10,2),
    category VARCHAR(50)
);

-- 插入table_1测试数据
INSERT INTO table_1 VALUES
('North', '2023Q1', 100.00, 'A'),
('North', '2023Q1', 200.00, 'B'),
('South', '2023Q2', 150.00, 'C'),
('South', '2023Q2', 300.00, 'D'),
('East', '2023Q3', 250.00, 'E');

-- 创建table_2
CREATE TABLE table_2 (
    REGION VARCHAR(50),
    PERIOD VARCHAR(50),
    other_value DECIMAL(10,2)
);

-- 插入table_2测试数据
INSERT INTO table_2 VALUES
('North', '2023Q1', 50.00),
('South', '2023Q2', 75.00),
('East', '2023Q3', 100.00);

核心查询语句 #

SELECT
    t1.REGION,
    t1.PERIOD,
    t1.some_value,
    t1.category,
    CASE 
        WHEN row_num = 1 THEN t2.other_value 
        ELSE NULL 
    END AS other_value
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY REGION, PERIOD ORDER BY category) AS row_num
    FROM table_1
) t1
LEFT JOIN table_2 t2 
    ON t1.REGION = t2.REGION 
    AND t1.PERIOD = t2.PERIOD
ORDER BY t1.REGION, t1.PERIOD, t1.row_num;

逻辑说明 #

1. 窗口函数分组编号：在子查询中使用ROW_NUMBER() OVER(PARTITION BY REGION, PERIOD ORDER BY category)，给每个REGION+PERIOD组内的行按category排序并分配唯一行号，组内第一行的行号为1。
2. 条件填充字段：通过CASE WHEN判断行号是否为1，仅当行号为1时取出table_2的other_value，其余行设为NULL。
3. 关联与排序：用LEFT JOIN保证table_1的所有行都被保留，最后按REGION、PERIOD和行号排序，确保结果分组有序。

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