Postgres多对多关联查询：筛选与组内所有子组关联的another_groups

嘿，这个需求我之前帮朋友处理过类似的，咱们一步步来搞定它：

我们要找的是和指定group下所有subgroups都建立了多对多关联的another_groups——简单说就是，这个another_groups必须和该group下的每一个subgroup都有一条关联记录在中间表subgroups_another_groups里，一个都不能少。

假设我们要查询group_id = 1的group对应的符合条件的another_groups，可以用下面的SQL：

SELECT ag.*
FROM another_groups ag
-- 关联中间表，拿到another_groups对应的subgroups关联
JOIN subgroups_another_groups sag ON ag.id = sag.another_group_id
-- 关联subgroups表，筛选出属于目标group的subgroups
JOIN subgroups s ON sag.subgroup_id = s.id
WHERE s.group_id = 1 -- 这里替换成你实际要查询的group ID
-- 按another_groups分组，方便统计关联的subgroups数量
GROUP BY ag.id, ag.name -- 这里要包含another_groups的所有字段，比如你表中有其他字段也要加上
-- 关键条件：统计的关联subgroups数量 = 目标group下的subgroups总数
HAVING COUNT(DISTINCT s.id) = (
    SELECT COUNT(*)
    FROM subgroups
    WHERE group_id = 1 -- 同样替换成目标group ID
);

• 前三行的JOIN操作：把another_groups、中间表、subgroups连起来，只保留属于目标group的subgroups关联数据；
• GROUP BY：把同一个another_groups的所有关联记录合并成一组；
• COUNT(DISTINCT s.id)：统计这个another_groups实际关联了多少个不同的subgroups（加DISTINCT是防止中间表有重复关联记录导致计数不准）；
• 子查询：算出目标group下总共有多少个subgroups；
• HAVING条件：只有当关联数量等于总数时，才说明这个another_groups关联了所有subgroups，符合我们的需求。

如果你的数据库支持CTE（比如MySQL 8+、PostgreSQL、SQL Server等），可以用下面的写法，逻辑更直观：

-- 先把目标group下的所有subgroups存成一个临时数据集
WITH target_subgroups AS (
    SELECT id
    FROM subgroups
    WHERE group_id = 1 -- 替换成目标group ID
)
SELECT ag.*
FROM another_groups ag
JOIN subgroups_another_groups sag ON ag.id = sag.another_group_id
JOIN target_subgroups ts ON sag.subgroup_id = ts.id
GROUP BY ag.id, ag.name
-- 直接和临时数据集的总数对比
HAVING COUNT(DISTINCT ts.id) = (SELECT COUNT(*) FROM target_subgroups);

• 如果你的数据库开启了ONLY_FULL_GROUP_BY模式（比如默认开启的MySQL 5.7+），GROUP BY必须包含another_groups表的所有非聚合字段，不能只写ag.id，否则会报错；
• 一定要用COUNT(DISTINCT ...)，如果中间表subgroups_another_groups里有重复的（比如同一个subgroup和another_groups关联了多次），不加DISTINCT会导致计数偏大，误判结果；
• 实际使用时，可以把固定的group_id换成参数，比如在Java/Python等应用程序里用预编译语句绑定参数，避免SQL注入同时提升复用性。

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