sql - 在多对多关系中选择匹配子集

Question

假设我在用户和项目之间存在多对多关系:一个用户可能属于多个项目，一个项目可能有多个用户。此关系在表 user_projects 中编码:

create table user_projects
(
proj_id int references projs(id) not null,
user_id int references users(id) not null,
primary key (proj_id, user_id)
);

这是我的问题:给定一组用户(user1、user2、...)，我想选择给定用户集是其所有用户的子集的所有项目。

例如，如果我在下面插入数据，然后询问用户 1 和 2 的所有项目，那么查询应该只返回项目 1。

insert into user_projects values (1, 1);
insert into user_projects values (1, 2);
insert into user_projects values (1, 3);
insert into user_projects values (2, 1);
insert into user_projects values (2, 3);

(如果最好的解决方案恰好是非标准的，我正在使用 PostgreSQL。)

编辑:为澄清起见，用户集应解释为对要返回的项目列表的限制。集合 {u1, u2} 意味着项目列表应该只包括那些至少有用户 u1 和 u2 的项目；集合 {u1} 表示应返回至少具有用户 u1 的所有项目，作为限制情况，空集表示应返回所有 项目。

Answer 1

Select project_ID 
from user_projects
where user_ID in (1,2)
group by project_ID
Having count(*) = 2

你知道你有 2 个用户，你知道他们将是唯一的(主键)所以您知道如果有 2 条记录，对于同一个项目，那么它就是您想要的一条。

您的问题表明您已收到 GIVEN 发送的用户，因此您知道有哪些用户以及有多少用户。上面的 SQL 可以更新为接受这些已知的参数，因此保持动态，不仅限于 2 个用户。

where user_ID in (userlist)
having count(*) = (cntuserList)

------------用户集为空时的处理-----

Select P.project_ID 
from Projects P
LEFT JOIN user_projects UP
where (UP.user_ID in (1,2) OR UP.USER_ID is null)
group by project_ID
Having count(*) = 2

这就是它的作用。它返回所有项目，如果有用户隶属于该项目，它会识别他们。如果您设置包含用户，则返回的项目列表将按该集合进行过滤，以确保整个集合通过 having 子句在项目中。

如果集合为空，LEFT join 连同 userID is null 语句将保留没有列出用户的项目，无论集合是否为空。 having 子句会将集合进一步减少到您在集合中定义的用户数，或者 0 表示返回所有未分配用户的项目。

我们还没有讨论的另外一个极端情况是，如果项目包含的用户多于您在集合中定义的用户，会发生什么情况。目前该项目将被退回；但我不确定这就是您想要的。

边注谢谢你让我思考。我不再深入研究代码了；这就是为什么我不时到这里来看看是否可以提供帮助!