用户-车辆多对多关系动态列数Pivot查询实现求助

嘿，这个问题我之前在做PostgreSQL多对多关联的报表时也碰到过！crosstab()函数虽然好用，但确实卡在了「必须提前确定返回列数」这个点上，针对你需要的动态展示用户关联车辆的需求，我有两个靠谱的解决方案，咱们一起来看看：

这个方案会自动计算用户关联车辆的最大数量，动态生成对应的id_carN和name_carN列，完美匹配你想要的输出格式：

DO $$
DECLARE
    max_cars INT;
    pivot_columns TEXT;
BEGIN
    -- 第一步：获取所有用户中关联车辆的最大数量
    WITH user_car_counts AS (
        SELECT fk_usr, COUNT(*) AS car_count
        FROM tbl_user_car
        GROUP BY fk_usr
    )
    SELECT MAX(car_count) INTO max_cars FROM user_car_counts;

    -- 第二步：动态拼接出所有需要的Pivot列（每组id_carN + name_carN）
    pivot_columns := string_agg(
        format(
            'MAX(CASE WHEN car_rank = %s THEN c.id_car END) AS id_car%s,
             MAX(CASE WHEN car_rank = %s THEN c.name END) AS name_car%s',
            n, n, n, n
        ),
        ', '
    )
    FROM generate_series(1, max_cars) AS n;

    -- 第三步：执行动态生成的最终查询
    EXECUTE format(
        'SELECT
            u.name AS name_usr,
            %s
        FROM tbl_users u
        LEFT JOIN (
            SELECT
                fk_usr,
                fk_car,
                ROW_NUMBER() OVER (PARTITION BY fk_usr ORDER BY fk_car) AS car_rank
            FROM tbl_user_car
        ) uc ON u.id_usr = uc.fk_usr
        LEFT JOIN tbl_cars c ON uc.fk_car = c.id_car
        GROUP BY u.id_usr, u.name
        ORDER BY u.id_usr',
        pivot_columns
    );
END $$;

代码解释： #

• 先用ROW_NUMBER()给每个用户的关联车辆按序号排名，这样每辆车都有一个唯一的car_rank（比如usr1的两辆车分别是1和2）
• 再用CASE WHEN配合MAX()，把每个排名对应的车辆ID和名称转成单独的列
• 通过动态SQL自动生成所有需要的列，不管用户关联多少辆车都能适配

如果不需要严格的扁平表格结构，用JSON聚合会更简洁高效，尤其适合后端程序处理数据的场景：

SELECT
    u.name AS name_usr,
    -- 把关联车辆信息聚合成JSON数组，空用户显示空数组
    COALESCE(
        json_agg(
            json_build_object(
                'id_car', c.id_car,
                'name_car', c.name
            )
        ),
        '[]'::json
    ) AS user_cars
FROM tbl_users u
LEFT JOIN tbl_user_car uc ON u.id_usr = uc.fk_usr
LEFT JOIN tbl_cars c ON uc.fk_car = c.id_car
GROUP BY u.id_usr, u.name
ORDER BY u.id_usr;

输出示例： #

这个方案不需要关心列数的问题，所有车辆信息都打包在一个JSON数组里，前端或者后端可以很方便地解析展示。

内容的提问来源于stack exchange，提问作者Julian David