PostgreSQL：如何关联tobe与asis表获取一对一匹配的结果集

有两张表tobe和asis，均包含以下字段：

• order_：整数类型
• id_：UUID类型
• text_：文本类型

其中tobe表存储的是正确的order_值，asis表中同一id_对应的order_值不正确。

表数据插入语句 #

INSERT INTO tobe (order_,id_,text_) VALUES
 (10,'22b48906-4d7f-4d22-a027-101d0a96b970',''),
 (11,'22b48906-4d7f-4d22-a027-101d0a96b970',''),
 (12,'22b48906-4d7f-4d22-a027-101d0a96b970',''),
 (13,'22b48906-4d7f-4d22-a027-101d0a96b970',''),
 (14,'22b48906-4d7f-4d22-a027-101d0a96b970',''),
 (15,'22b48906-4d7f-4d22-a027-101d0a96b970','');

INSERT INTO asis (order_,id_,text_) VALUES
 (7,'22b48906-4d7f-4d22-a027-101d0a96b970',''),
 (8,'22b48906-4d7f-4d22-a027-101d0a96b970',''),
 (9,'22b48906-4d7f-4d22-a027-101d0a96b970',''),
 (10,'22b48906-4d7f-4d22-a027-101d0a96b970',''),
 (11,'22b48906-4d7f-4d22-a027-101d0a96b970',''),
 (12,'22b48906-4d7f-4d22-a027-101d0a96b970','');

需求说明 #

需要生成一个结果集，包含tobe表的正确order_（命名为order_tobe）、id_、text_，以及asis表的错误order_（命名为order_asis），最终结果共6行。

直接通过id_关联两张表会得到36行笛卡尔积，不符合需求，期望的结果格式如下：

(order_tobe, id_, text_, order_asis)
(10,'22b48906-4d7f-4d22-a027-101d0a96b970','', 7),
(11,'22b48906-4d7f-4d22-a027-101d0a96b970','', 8),
(12,'22b48906-4d7f-4d22-a027-101d0a96b970','', 9),
(13,'22b48906-4d7f-4d22-a027-101d0a96b970','', 10),
(14,'22b48906-4d7f-4d22-a027-101d0a96b970','', 11),
(15,'22b48906-4d7f-4d22-a027-101d0a96b970','', 12);

解决方案 #

使用窗口函数ROW_NUMBER()为每个id_分组内的记录按order_排序生成行号，再通过id_和行号关联两张表，实现同顺序记录的一一匹配：

SELECT 
    t.order_ AS order_tobe,
    t.id_,
    t.text_,
    a.order_ AS order_asis
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id_ ORDER BY order_) AS rn
    FROM tobe
) t
JOIN (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id_ ORDER BY order_) AS rn
    FROM asis
) a ON t.id_ = a.id_ AND t.rn = a.rn;

原理说明 #

• PARTITION BY id_：按id_对记录分组，确保同一id_的记录在同一分组内处理
• ORDER BY order_：在分组内按order_排序，保证行号按order_顺序生成
• 最终通过id_和行号rn关联，让两张表中同位置的记录一一对应，得到6行符合要求的结果

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