sql - PostgreSQL条件连接

假设我有以下表格-

smallville=# create table contacts (name varchar(16), address_id int);
CREATE TABLE
smallville=# create table addresses (address_id int, address varchar(16));
CREATE TABLE
smallville=# create table partners (name1 varchar(16), name2 varchar(16));
CREATE TABLE

smallville=# insert into contacts values ('Clark Kent', NULL), ('Loise Lane', 1);
INSERT 0 2
smallville=# insert into addresses values (1, 'Manhattan'), (2, 'North Pole');
INSERT 0 2
smallville=# insert into partners values ('Clark Kent', 'Loise Lane'),
             ('Loise Lane', 'Clark Kent') ;
INSERT 0 2

我可以得到姓名和地址-

smallville=# select c.name, a.address from contacts c
             left outer join addresses a
             on c.address_id = a.address_id ;
    name    |  address
------------+-----------
 Clark Kent | (NULL)
 Loise Lane | Manhattan
(2 rows)

但是，如果缺少某人的地址，我该如何显示他/她的伴侣的地址？ --

    name    |  address
------------+-----------
 Clark Kent | Manhattan
 Loise Lane | Manhattan
(2 rows)

谢谢。

最佳答案

select c.name, coalesce(a.address, a1. address) from contacts c
left outer join addresses a on c.address_id = a.address_id
left outer join partners on c.name=partners.name1
left outer join contacts c1 on c1.name=partners.name2
left outer join addresses a1 on c1.address_id = a1.address_id;

关于sql - PostgreSQL条件连接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6168359/