PostgreSQL多表关联避免重复行，实现指定格式输出

针对你遇到的多表关联因笛卡尔积导致行数膨胀，需要对齐到最大记录数（Courses表的3条）的需求，核心思路是给每个表的记录按分组添加行号，然后通过行号+原关联键进行匹配，确保最终结果行数等于最大记录数，无匹配字段自动填充null。

具体实现步骤 #

1. 给各表生成行号

   • 对于记录数少于目标行数（3条）的表：
     • Base表（仅1条）：通过CROSS JOIN生成对应3行的记录，每行绑定行号1-3，确保在最终结果的每一行都能显示Base数据。
     • Child/School表（各2条）：按parent_id分组，用ROW_NUMBER()给每组内的记录编号（1、2）。
   • 基准表Courses（3条）：同样按parent_id分组生成行号（1、2、3），作为最终结果的行数基准。

2. 通过行号关联所有表
   以带行号的Courses表为主表，左连接其他带行号的子查询，关联条件同时包含行号和原关联键（如parent_id），避免笛卡尔积。

示例SQL代码 #

SELECT 
    -- 按需选择需要输出的字段，避免*带来的重复列（如rn）
    b.id AS base_id, b.base_field1, b.base_field2,
    c.id AS child_id, c.child_field1, c.child_field2,
    s.id AS school_id, s.school_field1, s.school_field2,
    crs.id AS course_id, crs.course_field1, crs.course_field2,
    -- 继续添加其他6张表的字段
    t1.id AS t1_id, t1.t1_field1,
    t2.id AS t2_id, t2.t2_field1
    -- ... 其他表字段
FROM (
    -- 基准表：Courses生成行号
    SELECT 
        crs.*,
        ROW_NUMBER() OVER(PARTITION BY crs.parent_id ORDER BY crs.id) AS rn
    FROM Courses crs
) crs
-- 关联Base表：生成3行对应每个行号
LEFT JOIN (
    SELECT 
        b.*,
        nums.rn
    FROM Base b
    CROSS JOIN (SELECT 1 rn UNION ALL SELECT 2 UNION ALL SELECT 3) nums
) b ON b.rn = crs.rn AND b.id = crs.parent_id -- 假设Base的id是Courses的parent_id，按实际关联关系调整
-- 关联Child表：按行号匹配
LEFT JOIN (
    SELECT 
        c.*,
        ROW_NUMBER() OVER(PARTITION BY c.parent_id ORDER BY c.id) AS rn
    FROM Child c
) c ON c.rn = crs.rn AND c.parent_id = b.id -- 假设Child的parent_id是Base的id，按实际调整
-- 关联School表：按行号匹配
LEFT JOIN (
    SELECT 
        s.*,
        ROW_NUMBER() OVER(PARTITION BY s.parent_id ORDER BY s.id) AS rn
    FROM School s
) s ON s.rn = crs.rn AND s.parent_id = c.id -- 假设School的parent_id是Child的id，按实际调整
-- 关联其他6张表的示例
LEFT JOIN (
    SELECT 
        t1.*,
        ROW_NUMBER() OVER(PARTITION BY t1.parent_id ORDER BY t1.id) AS rn
    FROM Table1 t1
) t1 ON t1.rn = crs.rn AND t1.parent_id = [关联的父表ID字段] -- 替换为实际关联键
LEFT JOIN (
    SELECT 
        t2.*,
        ROW_NUMBER() OVER(PARTITION BY t2.parent_id ORDER BY t2.id) AS rn
    FROM Table2 t2
) t2 ON t2.rn = crs.rn AND t2.parent_id = [关联的父表ID字段]
-- ... 剩余4张表按此逻辑添加

关键说明 #

• 行号的作用：通过行号限制每个表在最终结果中每行最多匹配一条记录，彻底避免一对多关联产生的笛卡尔积。
• Base表的处理：因为只有1条记录，通过CROSS JOIN生成3行，保证在最终的3条结果中都能显示Base的数据。
• 关联条件调整：请根据你的实际表关联关系（如parent_id对应的父表字段）修改ON子句中的关联键，确保逻辑正确。

内容的提问来源于stack exchange，提问作者Senyo Aborgah