從相同 ID 索引的不同表中獲取數據

數據庫：postgresql 12. 我有一張表people：

person_id | name
----------+-----
1         |John
2         |Paul
3         |Jim
...

每個人可以有零個或多個汽車保險契約，所以我有另一個表car_contracts如下：

contract_id | person_id | contract_number | date_from | insured_value | car_tag | .... (more fields)
------------+-----------+-----------------+-----------+---------------+---------+ 
1           | 1         | C99             |2020-01-01 | 5000          | ABC     | ....
2           | 1         | C02             |2020-03-01 | 4000          | XYZ     | ....
3           | 3         | C96             |2020-02-02 | 12000         | KKK     | ....
4           | 1         | C01             |2019-01-01 | 5500          | XXX     | ....
5           | 2         | C02             |2019-06-01 | 1000          | 666     | ....
6           | 3         | C03             |2020-02-20 | 11000         | KPX     | ....
...

每個人也可以擁有零個或多個發電廠（或其他任何東西；關鍵是，另一個具有不同欄位但仍通過 與人聯繫在一起person_id的表），所以表plants就像

plant_id | person_id | plant_name | power | address     | .... (more fields)
---------+-----------+------------+-------+-------------+ 
1        | 2         | plant01    |100    | 230 rue st. | ....
2        | 3         | jimsplant1 |50     | ...         | ....
3        | 3         | jimsplant2 |65.5   |             | ....
4        | 2         | plant02    |12.5   |             | ....
5        | 1         | house01    |20     |             | ....
6        | 1         | house02    |20     |             | ....
...

我可以通過將表格分別連接people到car_contracts和表格來獲得一份人員名單和他們的契約或人員名單。plants是否有可能通過一次查詢以某種方式一次獲取所有資訊（具有契約和工廠的人）？

在我看來，主要問題是契約的數量和工廠的數量沒有關係，並且可以有很大的不同，所以我認為加入與工廠簽訂契約的人沒有什麼意義。

好吧，您可以將行聚合到數組中：

select p.person_id, p.name, 
      array_agg(cc) as contracts,
      array_agg(pl) as plants
from people p
 left join car_contracts cc on p.person_id = cc.person_id
 left join plants pl on p.person_id = pl.person_id
group by p.person_id;

（這假設person_id被聲明為主鍵）

將行聚合為 json 值可能更容易在前端使用：

select p.person_id, p.name, 
      json_agg(to_jsonb(cc)) as contracts,
      jsong_agg(to_jsonb(pl)) as plants
from people p
 left join car_contracts cc on p.person_id = cc.person_id
 left join plants pl on p.person_id = pl.person_id
group by p.person_id;

為避免重複，請在派生表中進行聚合：

select p.person_id, p.name, 
      cc.contracts,
      pl.plants
from people p
 left join (
   select person_id, jsonb_agg(to_jsonb(c)) as contracts
   from car_contracts c
   group by person_id
 ) cc on p.person_id = cc.person_id
 left join (
   select person_id, jsonb_agg(to_jsonb(p)
   from plants 
   group by person_id
 ) pl on p.person_id = pl.person_id;

引用自：https://dba.stackexchange.com/questions/265397