sql - 检索数据库关联，不存在关联时用NULL值填充

Question

我有三个 Postgresql 表关联到另一个表:

用户(用户名、名字、姓氏)

地址(id_address，id_user，地址)

BANK_CARD(id_bank_card、id_user、号码)

网络(id_network、id_user、状态)

一个用户可以有多个地址、银行卡和网络。

例如 USER 表:

id_user    first_name     last_name
-------------------------------------
 1          John           Doe
 2          David          Smith

对于 ADDRESS 表:

id_address    id_user    address
----------------------------------
 10            1          address1

对于 BANK_CARD:

id_bank_card    id_user     number
-----------------------------------
 20              1           1234
 21              1           5678

对于网络:

id_network    id_user    status
--------------------------------
 30            1          status1
 31            1          status2
 32            1          status3

我想检索用户和其他表之间的现有关联，如下所示:

id_user  id_address  address  id_bank_card  number  id_network  status
-----------------------------------------------------------------------
 1        10         address1      20       1234       30       status1
 1        NULL       NULL          21       5678       31       status2
 1        NULL       NULL          NULL     NULL       32       status3

我尝试过使用 JOIN 或 UNION 进行不同的查询，但没有任何效果。感谢您的帮助。

Answer 1

一种方法是使用 union all 和聚合:

select id_user, max(id_address) as id_address,
       max(id_bank_card) as id_bank_card,
       max(number) as number,
       max(id_network) as id_bank_card,
       max(status) as status)
from ((select id_user, id_address, NULL as id_bank_card, NULL as number,
              NULL as id_network, NULL as status
              row_number() over (partition by id_user order by id_address) as seqnum
       from address
      ) union all
      (select id_user, NULL as id_address, id_bank_card, number,
              NULL as id_network, NULL as  status
              row_number() over (partition by id_user order by id_bank_card) as seqnum
       from bank_card
      ) union all
      (select id_user, NULL as id_address, NULL as id_bank_card, NULL as number,
              id_network, status
              row_number() over (partition by id_user order by id_network) as seqnum
       from network
      )
     ) abcn
group by id_user, seqnum;

您可能会发现将结果放在数组中更实用:

select *
from (select id_user, array_agg(id_address) as id_addresses
      from address
      group by id_user
     ) a full outer join
     (select id_user, array_agg(id_bank_card) as id_bank_cards,
             array_agg(number) as numbers
      from bank_card
      group by id_user
     ) bc
     using (id_user) full outer join
     (select id_user, array_agg(id_network) as id_networds,
             array_agg(status) as statuses
      from network
      group by id_user
     ) n
     using (id_user);