在多对多关系中选择匹配子集 [英] Selecting matching subset in many-to-many relation

问题描述

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

Suppose I have a many-to-many relation between users and projects: one user may belong to several projects and one project may have multiple users. This relation is encoded in table 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，...），我要选择给定用户集为其所有用户的子集的所有项目。

Here's my problem: given a set of users (user1, user2, ...), I want to select all projects for which the given set of users is a subset of all its users.

例如，如果我在下面插入数据，然后要求所有具有用户1和2的项目，则查询应仅返回项目1。

If, for example, I were insert the data below and then ask for all projects with users 1 and 2, then the query should return only project 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。）

(I'm using PostgreSQL, if the best solution happens to be non-standard.)

编辑：为澄清起见，应将用户集解释为对要返回的项目列表的约束。集合{u1，u2}表示项目列表应仅包括那些至少具有用户u1和u2的项目；集合{u1}表示应返回至少具有用户u1的所有项目，而在有限情况下，空集合表示应返回 all 个项目。

For clarification, the set of users should be interpreted as a constraint on the list of projects to return. The set {u1, u2} means that the list of projects should include only those projects having at least users u1 and u2; the set {u1} means that all projects having at least user u1 should be returned, and as a limiting case, the empty set means that all projects should be returned.

推荐答案

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

您知道您有2位用户，您知道它们将是唯一的（主键）
，因此，您知道如果有2条记录，那么对于同一项目，就是您想要的一条记录。

You know that you have 2 users, you know that they will be unique (primary key) so you know that if there are 2 records, for the same project then it's one you want.

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

Your question indicated you have a GIVEN sent of users therefor you know what users and how many there are. the above SQL could be updated to accept parameters for these known and thus remains dynamic, not limited to just 2 users.

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

---- -------处理用户为空的情况-----

-----------To handle situation when set of users is empty-----

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

所以这是这样做的。它返回所有项目，并且如果有与该项目相关的用户，它将对其进行标识。
如果您的集合包含用户，则返回的项目列表将通过该集合进行过滤，以确保整个集合都通过hading子句在项目中。

So here's what this does. It returns all projects and if there's a user affiliated to that project it identifies them. If you set contains users, the list of projects returned is filtered by that set ensuring that the entire set is in the project through the having clause.

如果如果集合为空，则LEFT联接以及userID为null语句将保留未列出用户的项目，无论集合是否为空。 hading子句会将集合进一步减少为您在集合中定义的用户数量，或者为0表示返回所有未分配用户的项目。

If the set is empty, the LEFT join along with the userID is null statement will keep the projects with no users listed regardless of if the set is empty or not. The having clause will further reduce the set to the # of users you defined in the set, OR 0 indicating return all projects with no users assigned.

尚未讨论的是，如果一个项目包含的用户数量超过了您在集合中定义的数量，那么应该怎么办。目前将退还该项目；

One additional edge case we didn't discuss yet is what should happen if a project contains more users than what you defined in the set. Presently this project would be returned; but i'm not positive that's what you wanted.

在旁注中，感谢您让我思考。我不再需要太多的代码了；这就是为什么我不时在这里拖钓看看是否可以帮忙！

on a side note thanks for making me think. I don't get to get into the code as much anymore; which is why I troll here from time to time to see if I can help!

这篇关于在多对多关系中选择匹配子集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！