sql - Postgres多对多关系:从相关表中查找与一组行相关的所有行

考虑三个表，我们称它们为groups、subgroups、another_groups和表subgroups_another_groups，它们指定了subgroups和another_groups之间的多对多关系。subgroups和groups是一对多关系，因此subgroups有外键group_id。
如何才能选择一个another_groups中所有subgroups都与之相关的group？

最佳答案

我想你说的是这样一种安排：

CREATE TABLE groups (
   id integer PRIMARY KEY
);

CREATE TABLE subgroups (
   id integer PRIMARY KEY,
   group_id integer REFERENCES groups NOT NULL
);
CREATE INDEX ON subgroups(group_id);

CREATE TABLE another_groups (
   id integer PRIMARY KEY
);

CREATE TABLE subgroups_another_groups (
   subgroup_id integer REFERENCES subgroups NOT NULL,
   another_groups_id integer REFERENCES another_groups NOT NULL,
   PRIMARY KEY(subgroup_id, another_groups_id)
);
CREATE INDEX ON subgroups_another_groups(another_groups_id);

然后您想知道所有通过其他两个表连接到aanother_groups的groups，除了有一个没有连接到这个another_groups的子组的表，对吗？
在SQL中，其内容如下：

SELECT DISTINCT g.id, a.id
FROM another_groups a
   JOIN subgroups_another_groups sag ON a.id = sag.another_groups_id
   JOIN subgroups s ON sag.subgroup_id = s.id
   JOIN groups g ON s.group_id = g.id
WHERE NOT EXISTS
         (SELECT 1 FROM subgroups s1
          WHERE s1.group_id = g.id
            AND NOT EXISTS
                   (SELECT 1 FROM subgroups_another_groups sag1
                    WHERE sag1.subgroup_id = s1.id
                      AND sag1.another_groups_id = a.id
                   )
         );