基于关联表将两行合并为一行——PostgreSQL 12实现问询

需求说明 #

现有agreements与party两张表，关联规则如下：

• 当party.indicator = 1时，party.c1 = agreements.c1且party.b1 = agreements.b1
• 当party.indicator = 0时，party.c1 = agreements.c2且party.b1 = agreements.b2

其中agreements表的(c1, b1, c2, b2)四列组合值唯一。

表数据 #

agreements表 #

c1    b1  c2    b2
1001  1   2001  1
1001  1   2001  2
1001  1   2002  1
1001  1   2003  1
1001  2   2004  1
1001  2   2004  5

party表 #

c1    b1  indicator  id
1001  1   1          id-1
1001  2   1          id-2
2001  1   0          id-3
2001  2   0          id-4
2002  1   0          id-5
2003  1   0          id-6
2004  1   0          id-7
2004  5   0          id-8

目标结果 #

需要将agreements表中每组(c1,b1)与(c2,b2)对应的party表行合并，得到如下格式：

c1    b1  indicator  id    c1    b1  indicator  id
1001  1   1          id-1  2001  1   0          id-3
1001  1   1          id-1  2001  2   0          id-4
1001  1   1          id-1  2002  1   0          id-5
1001  1   1          id-1  2003  1   0          id-6
1001  2   1          id-2  2004  1   0          id-7
1001  2   1          id-2  2004  5   0          id-8

实现方案 #

通过两次关联party表即可实现需求，具体SQL语句如下：

SELECT
    p1.c1, p1.b1, p1.indicator, p1.id,
    p2.c1, p2.b1, p2.indicator, p2.id
FROM agreements a
JOIN party p1 ON p1.c1 = a.c1 AND p1.b1 = a.b1 AND p1.indicator = 1
JOIN party p2 ON p2.c1 = a.c2 AND p2.b1 = a.b2 AND p2.indicator = 0
ORDER BY p1.id, p2.id;

逻辑说明 #

1. 第一次关联party表（别名p1）：匹配indicator=1的行，关联条件对应agreements的(c1,b1)组合，取出对应party数据。
2. 第二次关联party表（别名p2）：匹配indicator=0的行，关联条件对应agreements的(c2,b2)组合，取出对应party数据。
3. 最终通过JOIN将两组数据按agreements的记录一一对应，排序后得到目标结果。

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