PostgreSQL多对多关联下获取唯一A组合的高效SQL方案咨询

表结构回顾 #

• 表A：id（integer）
• 表B：id（integer）
• 中间表C：id（integer）、a_id（关联A.id）、b_id（关联B.id）

问题1：有没有更高效的方法得到唯一组合？ #

当前循环查询的方式会产生海量SQL请求（200条A × 10000条B = 200万次查询），效率极低。推荐直接在数据库层面完成聚合、排序和去重，大幅减少网络IO和应用层处理压力：

针对单个A记录的查询

比如要获取ID为1的A记录关联的B所对应的唯一A组合：

WITH linked_b AS (
    -- 先拿到A1关联的所有B
    SELECT DISTINCT c.b_id
    FROM c
    WHERE c.a_id = 1
),
b_a_groups AS (
    -- 对每个B，生成有序的A ID数组（排序是为了让[A1,A2]和[A2,A1]视为同一组合）
    SELECT 
        array_agg(c.a_id ORDER BY c.a_id) AS a_id_group
    FROM c
    JOIN linked_b ON c.b_id = linked_b.b_id
    GROUP BY c.b_id
)
-- 对数组去重，得到唯一组合
SELECT DISTINCT a_id_group
FROM b_a_groups;

针对所有A记录的批量查询

如果要一次性处理所有A记录的需求：

WITH a_linked_b AS (
    -- 建立A与关联B的映射
    SELECT 
        c.a_id,
        c.b_id
    FROM c
),
b_a_groups AS (
    -- 对每个A关联的B，生成对应的有序A ID数组
    SELECT 
        alb.a_id,
        array_agg(c.a_id ORDER BY c.a_id) AS a_id_group
    FROM a_linked_b alb
    JOIN c ON alb.b_id = c.b_id
    GROUP BY alb.a_id, alb.b_id
)
-- 按A分组，输出唯一组合
SELECT 
    a_id,
    a_id_group
FROM b_a_groups
GROUP BY a_id, a_id_group
ORDER BY a_id;

性能优化建议

给中间表C创建两个联合索引，大幅提升关联和聚合效率：

CREATE INDEX idx_c_a_b ON c(a_id, b_id);
CREATE INDEX idx_c_b_a ON c(b_id, a_id);

问题2：是否可以通过单条SQL获取所有组合（不进行去重）？ #

完全可以，直接通过关联中间表就能拿到所有原始组合，包括顺序不同的重复组合：

原始明细记录（每条B关联的A单独行）

-- 获取A1关联的所有B及其对应的每条A记录
SELECT 
    c1.b_id AS b_id,
    c2.a_id AS related_a_id
FROM c c1
JOIN c c2 ON c1.b_id = c2.b_id
WHERE c1.a_id = 1
ORDER BY c1.b_id;

按B分组的A列表（保留原始顺序）

如果需要按B分组展示对应的A集合（不排序，保留原始关联顺序）：

SELECT 
    c1.b_id AS b_id,
    array_agg(c2.a_id) AS related_a_list
FROM c c1
JOIN c c2 ON c1.b_id = c2.b_id
WHERE c1.a_id = 1
GROUP BY c1.b_id
ORDER BY c1.b_id;

这样就能得到所有未去重的组合，比如B4的[A2,A1]和B1的[A1,A2]会被视为两个不同的列表。

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