通过使用具有相同主键的第三个表来连接具有公共字段的两个表

Question

我正试图在3个表之间创建一个联接。表中的2个表共享大部分字段(只有几个字段不同(。我想知道如何创建这个联接，将公共字段合并在一列中，将不存在数据时不常见的字段留空。

table 1:
PK1 geom field 2 field 3 field 4 field 5
1    1-1     1-2     1-3     1-4     1-5    
2    2-1     2-2     2-3     2-4     2-5   
3    3-1     3-2     3-3     3-4     3-5   
4    4-1     4-2     4-3     4-4     4-5   
5    5-1     5-2     5-3     5-4     5-5   
table a:
PK1 field 1ab field 2ab field 3ab field 4ab field 5a
1    1-1a       1-2a         1-3a     1-4a     1-5a     
2    2-1a       2-2a         2-3a     2-4a     2-5a     
3    3-1a       3-2a         3-3a     3-4a     3-5a      
4    4-1a       4-2a         4-3a     4-4a     4-5a      
5    5-1a       5-2a         5-3a     5-4a     5-5a

table b:
PK1 field 1ab field 2ab field 3ab field 4ab field 5b field 6b
6    1-1b       1-2b      1-3b     1-4b        1-5b      1-6b
7    2-1b       2-2b      2-3b     2-4b        2-5b      2-6b
8    3-1b       3-2b      3-3b     3-4b        3-5b      3-6b
9    4-1b       4-2b      4-3b     4-4b        4-5b      4-6b
10   5-1b       5-2b      5-3b     5-4b        5-5b      5-6b

结果表加入表1 PK:

PK1  geom    field 2ab field 3ab field 4ab field 5a field 5b field 6b
1    1-1       1-2a      1-3a      1-4a      1-5a     null     null
2    2-1       1-2a      1-3a      1-4a      1-5a     null     null 
3    3-1       1-2a      1-3a      1-4a      1-5a     null     null
4    4-1       1-2a      1-3a      1-4a      1-5a     null     null
5    5-1       1-2b      1-3b      1-4b      null     null     null
6    6-1       1-2b      1-3b      1-4b      null     1-5b     1-6b
7    7-1       1-2b      1-3b      1-4b      null     1-5b     1-6b
8    8-1       1-2b      1-3b      1-4b      null     1-5b     1-6b
9    9-11       1-2b      1-3b      1-4b      null     1-5b     1-6b

我已经匹配了所有具有内部联接的列，但我无法获得我想要的结果，有什么想法可以获得吗？我在这里检查过，但找不到任何答案，我也尝试过合并，但没有得到我期望的结果。

这是我获取每个表的所有字段的查询类型，所以有很多字段是重复的。

SELECT t1.pk,
t2.field 1ab,
t2.field 1ab... t3.field 5b...
FROM table 1 t1
LEFT JOIN table b t2 ON t1.pk::text = t2.pk::text
LEFT JOIN ocod t3 ON t3.pk::text = t1.pk::text
GROUP BY
t1.pk,
t2.field 1ab,
t2.field 1ab...

Answer 1

我在这本书上学到了一些东西。在您的示例中，我看不出表t1与所需结果之间的关系。期望的结果似乎只是t2和t3的组合。我从这个Answer中得到了解决方案的核心，它使用了一个NATURAL JOIN。

首先创建表格：

BEGIN;
CREATE TABLE t1 (
PK1     text,
field_1 text,
field_2 text,
field_3 text,
field_4 text,
field_5 text
);
INSERT INTO t1 VALUES
('1', '1-1', '1-2', '1-3', '1-4', '1-5'), 
('2', '2-1', '2-2', '2-3', '2-4', '2-5'),
('3', '3-1', '3-2', '3-3', '3-4', '3-5'), 
('4', '4-1', '4-2', '4-3', '4-4', '4-5'),
('5', '5-1', '5-2', '5-3', '5-4', '5-5');
CREATE TABLE t2 (
PK1       text,
field_1ab text,
field_2ab text,
field_3ab text,
field_4ab text,
field_5a  text
);
INSERT INTO t2 VALUES
('1',  '1-1a',  '1-2a',  '1-3a',  '1-4a',  '1-5a'), 
('2',  '2-1a',  '2-2a',  '2-3a',  '2-4a',  '2-5a'),
('3',  '3-1a',  '3-2a',  '3-3a',  '3-4a',  '3-5a'), 
('4',  '4-1a',  '4-2a',  '4-3a',  '4-4a',  '4-5a'),
('5',  '5-1a',  '5-2a',  '5-3a',  '5-4a',  '5-5a');
CREATE TABLE t3 (
PK1       text,
field_1ab text,
field_2ab text,
field_3ab text,
field_4ab text,
field_5b  text,
field_6b  text
);
INSERT INTO t3 VALUES
('6', '1-1b', '1-2b', '1-3b', '1-4b', '1-5b', '1-6b'), 
('7', '2-1b', '2-2b', '2-3b', '2-4b', '2-5b', '2-6b'),
('8', '3-1b', '3-2b', '3-3b', '3-4b', '3-5b', '3-6b'), 
('9', '4-1b', '4-2b', '4-3b', '4-4b', '4-5b', '4-6b'),
('10', '5-1b', '5-2b', '5-3b', '5-4b', '5-5b', '5-6b');
COMMIT;

现在是查询。似乎如果你做一个自然的全外部联接，PostgreSQL会尝试组合尽可能多的列。

SELECT *
FROM (t2 NATURAL FULL OUTER JOIN t3)
ORDER BY pk1::int

它给出

>ield _5afield_5b<1-2a>>-5a<2-2a>>1>>1[NOLL]-6btd>7<2-2b>>12-6b>4-3b-4b>td>4-6b>>空]15-6b

pk1	field_1ab	field_2ab	field_3ab	field _4ab
1	1-1a	1-3a	1-4a		[NULL]	[NOLL]
2	2-1a	2-3a	2-5a	[NULL]	[NOLL]
3	3-1a	3-2a	3-3a	3-5a	[NULL]	[NULL]
	4-1a	4-2a	4-3a	4-5a	[NULL]	[NOLL]
5	5-1a	5-2a	5-3a	5-5a	[NULL]
	1-1b	1-2b	1-3b	1-4b	[空]	1-5b	2-1b	2-3b	2-4b	[空]	2-5b
	3-1b	3-2b	3-3b	3-4b	[空]	3-5b	3-6b
9	4-1b	4-2b	[空]	4-5b
10	5-1b	5-2b	5-3b	5-4b	5-5b