SQL:查询包含一组精确用户的组 [英] SQL: Query for a group that contains an exact set of users

问题描述

如果我有一个简单的用户和组的多对多连接表，例如:

If I have a simple many-to-many join table of users and groups such as:

CREATE TABLE IF NOT EXISTS `users`  (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `user_group` (
  `user_id` int(6) unsigned NOT NULL,
  `group_id` int(6) unsigned NOT NULL,
  PRIMARY KEY (`user_id`, `group_id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `groups` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `users` (`id`, `name`) VALUES
  ('1', 'Michael'),
  ('2', 'Sarah'),
  ('3', 'Steven'),
  ('4', 'Jane');

INSERT INTO `groups` (`id`, `name`) VALUES
  ('1', 'M Names'),
  ('2', 'S Names'),
  ('3', 'J Names'),
  ('4', 'Men'),
  ('5', 'Women');

INSERT INTO `user_group` (`user_id`, `group_id`) VALUES
  ('1', '1'),
  ('1', '4'),
  ('2', '2'),
  ('2', '5'),
  ('3', '2'),
  ('3', '4'),
  ('4', '3'),
  ('4', '5');

我想弄清楚如何查看一组给定的用户是否构成现有组.

I am trying to figure out how to see if a given set of users makes up an existing group.

例如，如果我查询 Michael，它会返回组 M Names，因为该组完全由 Michael 组成.

For example, if I query for Michael, it returns the group M Names because that group consists entirely of Michael.

如果我查询 (Sarah, Steven)，它会返回组S Names".

If I query for (Sarah, Steven), it returns the group 'S Names'.

如果我查询 (Sarah, Michael)，则不会返回任何组.

If I query for (Sarah, Michael), no group is returned.

查询一组给定用户所在的组很容易:

It is easy to query for the groups that a given set of users are in:

SELECT * FROM `user_group`
WHERE `user_id` in ('2', '3');

但我不知道如何将其限制为所有给定用户所属的组.

But I am at a loss on how to limit that to only the groups that all of the given users are a member of.

推荐答案

(已更新，添加一些内容使这不仅仅是一个子集而是完全匹配)

(updated, add something to make this not just a subset but an exact match)

我假设您想要所有用户都是成员的组.或者用户是一个子集.以下查询将完成这项工作(除非我犯了错误)

I assume you want the groups where all the users are members of together. or where the users are a subset. the following query will do the job (unless I made a mistake)

SELECT g.Name
FROM groups g
INNER JOIN user_group ug on (ug.group_id=g.id)
WHERE ug.user_id IN (1,4)
GROUP BY g.id
HAVING COUNT(ug.user_id) = 2

说明:首先，您基本上(可能)希望通过 user_id 过滤 user_group 以查找包含任何用户 ID(WHERE>-clause)，然后您要选择具有所有用户 ID 的组(GROUP 和 HAVING-clause).

explanation: first, you essentially (probably) want to filter user_group by user_id to find groups containing ANY of the users' ids (WHERE-clause), then you want to select the groups, that have ALL the users' ids (GROUP and HAVING-clause).

然而，这个查询有两个不同的部分:where 子句中的用户 id(在本例中为 1,4)和 have 子句中那些 user_id 的计数(2 在这种情况下).

However, this query has two varying parts: the user ids in the where clause (1,4 in this case) and the count of those user_ids in the having clause (2 in this case).

更新

现在，由于您的帖子暗示您想要完全匹配，您可以添加以下内容到HAVING-clause

Now, since your post suggests you want an exact match, you can add the following to the HAVING-clause

   AND COUNT(ug.user_id) = (SELECT COUNT(*) FROM user_group ug2 WHERE ug2.group_id=g.id)

确保该组的用户数与查询用户数相同.

which ensures the counts of users of that group is the same as the query user count.

这篇关于SQL:查询包含一组精确用户的组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！