PostgreSQL多关联表场景下，如何用单条高效SQL获取符合条件的全量数据？

完全可以用单条SQL实现需求，且能保证高效性，核心思路是先精准筛选出同时使用B1和B3品牌轮胎的汽车，再关联获取完整信息并聚合为目标JSON格式。

核心SQL语句 #

SELECT json_agg(
    json_build_object(
        'id', c.id,
        'plate', c.plate,
        'engine', json_build_object(
            'id', e.id,
            'model', e.model
        ),
        'tires', t.tire_list
    )
) AS result
FROM "Car" c
JOIN "Engine" e ON e.id = c.engine
JOIN (
    -- 筛选同时拥有B1和B3轮胎的汽车ID
    SELECT ct.car
    FROM "Cars_Tires" ct
    JOIN "Tire" t ON ct.tire = t.id
    WHERE t.brand IN ('B1', 'B3')
    GROUP BY ct.car
    HAVING COUNT(DISTINCT t.brand) = 2
) filtered_cars ON filtered_cars.car = c.id
JOIN (
    -- 聚合每辆车的所有轮胎为JSON数组
    SELECT 
        ct.car,
        json_agg(
            json_build_object('id', t.id, 'brand', t.brand)
        ) AS tire_list
    FROM "Cars_Tires" ct
    JOIN "Tire" t ON ct.tire = t.id
    GROUP BY ct.car
) t ON t.car = c.id;

语句说明 #

1. 筛选符合条件的汽车：子查询filtered_cars通过关联轮胎表，筛选出同时包含B1和B3品牌轮胎的汽车ID，使用GROUP BY+HAVING COUNT(DISTINCT t.brand)=2确保两个品牌都存在，避免用OR带来的冗余数据。
2. 聚合轮胎数据：子查询t提前把每辆车的所有轮胎聚合为JSON数组，避免多次关联导致的行膨胀。
3. 构造最终JSON：用json_build_object构造单辆车的完整结构，再通过json_agg把所有汽车聚合为顶层数组，直接输出目标格式。

性能优化建议 #

• 为以下字段建立索引：
  • Tire(brand)：加速品牌筛选
  • Cars_Tires(car, tire)：加速汽车与轮胎的关联查询
  • Car(id)、Engine(id)：主键索引默认存在，确保关联效率
• 由于每辆车最多4个轮胎，聚合操作的开销极低，即使百万级数据量也能高效运行。

通用SQL兼容思路（非PostgreSQL专属） #

如果需要兼容其他数据库，可以用字符串拼接模拟JSON结构（例如用CONCAT、GROUP_CONCAT等函数），但性能和可读性不如PostgreSQL原生JSON函数。示例思路：

-- 仅为通用思路，不同数据库函数语法有差异
SELECT 
    CONCAT('[', GROUP_CONCAT(
        CONCAT(
            '{"id":', c.id,
            ',"plate":"', c.plate,
            '","engine":{"id":', e.id, ',"model":"', e.model, '"}',
            ',"tires":[', t.tire_str, ']}'
        ) SEPARATOR ','
    ), ']') AS result
FROM "Car" c
-- 其余关联和筛选逻辑同上，仅聚合部分替换为字符串拼接

内容的提问来源于stack exchange，提问作者Carmelo Acosta