PostgreSQL基于多列匹配从另一表更新表字段的实现方案

需求说明 #

需根据以下规则更新table_2的pkt_type与t_no字段：

• table_2的lead_id需匹配table_1的lead_id、lead_a_id或lead_b_id
• table_2的datetimestamp必须落在table_1的start_datetimestamp与end_datetimestamp之间（包含边界值）
• 匹配类型对应规则：
  • 若匹配table_1的lead_id，table_2的pkt_type设为"Lead"
  • 若匹配table_1的lead_a_id或lead_b_id，table_2的pkt_type设为"Not Lead"
• 同步将table_2的t_no更新为匹配到的table_1对应t_no

样本数据准备 #

建表语句 #

CREATE TABLE table_1 (
    lead_id VARCHAR(20),
    lead_a_id VARCHAR(20),
    lead_b_id VARCHAR(20),
    start_datetimestamp DATETIME,
    end_datetimestamp DATETIME,
    t_no VARCHAR(20)
);

CREATE TABLE table_2 (
    lead_id VARCHAR(20),
    datetimestamp DATETIME,
    pkt_type VARCHAR(20),
    t_no VARCHAR(20)
);

插入样本数据 #

-- 插入table_1数据
INSERT INTO table_1 VALUES
('L1', 'A1', 'B1', '2024-01-01 00:00:00', '2024-01-31 23:59:59', 'T001'),
('L2', 'A2', 'B2', '2024-02-01 00:00:00', '2024-02-29 23:59:59', 'T002'),
('L3', 'A3', 'B3', '2024-03-01 00:00:00', '2024-03-31 23:59:59', 'T003');

-- 插入table_2数据
INSERT INTO table_2 VALUES
('L1', '2024-01-15 12:00:00', NULL, NULL),
('A1', '2024-01-10 09:00:00', NULL, NULL),
('B2', '2024-02-20 14:00:00', NULL, NULL),
('L4', '2024-01-05 08:00:00', NULL, NULL), -- 无匹配记录
('A3', '2024-04-01 10:00:00', NULL, NULL); -- 时间不在范围内

更新SQL语句 #

UPDATE table_2
JOIN table_1 ON 
    table_2.lead_id IN (table_1.lead_id, table_1.lead_a_id, table_1.lead_b_id)
    AND table_2.datetimestamp BETWEEN table_1.start_datetimestamp AND table_1.end_datetimestamp
SET 
    table_2.pkt_type = CASE
        WHEN table_2.lead_id = table_1.lead_id THEN 'Lead'
        ELSE 'Not Lead'
    END,
    table_2.t_no = table_1.t_no;

逻辑说明 #

• 通过JOIN同时关联两张表的lead_id匹配条件与时间范围条件，仅处理符合双条件的记录
• 用CASE分支判断匹配的字段类型，设置对应的pkt_type值
• 直接将匹配到的table_1.t_no同步赋值给table_2.t_no

预期更新结果 #

执行更新后，table_2的数据如下：

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