sql - 如何编写用于以下的 sql 选择查询

Question

我有以下表格

表一：人

columns: id,name,address,code

表 2：汽车详情

columns: id,person_id,car_brand
constraints: FL==>carDetails(person_id) reference person(id)
Note: carDetails is having multiple details for single person

表 3：mobileDetails

columns: id,person_id,mobile_brand
constraints: FL==>mobileDetails(person_id) reference person(id)
Note: mobileDetails is having multiple details for single person

同样，我有很多细节，例如汽车和个人手机

我要选择的是：

person(id),
person(name),
Array of carDetails(brand) belonging to that particular person(id)
Array of mobileDetails(brand) belonging to that particular person(id)

标签： sqlpostgresql

Answer 1

您应该使用聚合的子查询编写此查询：

select p.*, c.car_brands, m.mobile_brands
from person p left join
     (select c.person_id, array_agg(cd.car_brand) as car_brands
      from car_details c
      group by c.person_id
     ) c
     on c.person_id = p.id left join
     (select m.person_id, array_agg(m.mobile_brand) as mobile_brands
      from mobile_details m
      group by m.person_id
     ) m
     on m.person_id = m.id;

两个注意事项：

• 您想使用left join，以防某些人的某个表中没有数据。
• 您希望在加入之前进行聚合以避免重复。尽管您可以添加它distinct会array_agg()导致性能损失。

如果您正在过滤人员，使用子查询或（等效地）作为横向连接通常更有效：

select p.*,
       (select array_agg(cd.car_brand) as car_brands
        from car_details c
        where c.person_id = p.id
       ) as car_brands,
       (select array_agg(m.mobile_brand) as mobile_brands
        from mobile_details m
        where m.person_id = p.id
       ) as mobile_brands
from person p;