Postgresql
從相同 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;