Postgresql join same table

Question

我有包含字段的表：

line_id, point_address:
1 | Address1
1 | Address2
2 | Address1
2 | Address3
3 | Address2
3 | Address3

对于结果，我需要每个line_id都有一行的表格，并且两个point_address值line_id单独的行中：

line_id,point_address_1,point_address_2:
1 | Address1 | Address2
2 | Address1 | Address3
3 | Address2 | Address3

我尝试创建自连接，但我得到的是每个line_id 4 个结果，其中包含 point_address 字段的所有组合。 获得单一结果的最佳方法是什么

Answer 1

假设每个不同的line_id只有两个地址，您可以通过强制对结果表中的point_address进行排序来解决此问题：

SELECT
l1.line_id, l1.point_address AS point_address1, 
l2.point_address AS point_address2
FROM line_address l1 JOIN line_Address l2 ON l1.line_id = l2.line_id
WHERE l1.point_address < l2.point_address;
line_id     point_address1  point_address2
----------  --------------  --------------
1           Address1        Address2      
2           Address1        Address3      
3           Address2        Address3   

请注意，这仅在上述假设下有效，因此如果我们添加

INSERT INTO line_address (line_id, point_address) VALUES (3, 'Address4');

选择产量

line_id     point_address1  point_address2
----------  --------------  --------------
1           Address1        Address2      
2           Address1        Address3      
3           Address2        Address3      
3           Address2        Address4      
3           Address3        Address4 

当您尝试将行变成列时，关系代数并不友好，反之亦然。