sql - (SQL) 根据 user_id[] 匹配用户属于哪个组

Question

用户表

ID | name 
1  | ada    
2  | bob   
3  | tom  

组表

ID | name
1  | group A
2  | group B
3  | group C

user_group 表

user_id | group_id
1       | 1
2       | 1
1       | 2
2       | 2
3       | 2
1       | 3
3       | 3

给定的用户 ID 组:[1, 2, 3]

如何查询以上列表中所有用户所属的组？ (在这种情况下:B组)

Answer 1

获取完全包含指定用户的所有组(即所有指定用户，没有其他用户)

DECLARE @numUsers int = 3

SELECT ug.group_id
       --The Max doesn't really do anything here because all
       --groups with the same group id have the same name.  The
       --max is just used so we can select the group name eventhough
       --we aren't aggregating across group names
     , MAX(g.name) AS name
FROM user_group ug
--Filter to only groups with three users
JOIN (SELECT group_id FROM user_group GROUP BY group_id HAVING COUNT(*) = @numUsers) ug2
  ON ug.group_id = ug2.group_id
JOIN [group] g
    ON ug.group_id = g.ID
WHERE user_id IN (1, 2, 3)
GROUP BY ug.group_id
--The distinct is only necessary if user_group
--isn't keyed by group_id, user_id
HAVING COUNT(DISTINCT user_id) = @numUsers

要获取包含所有指定用户的组:

    DECLARE @numUsers int = 3    

    SELECT ug.group_id
           --The Max doesn't really do anything here because all
           --groups with the same group id have the same name.  The
           --max is just used so we can select the group name eventhough
           --we aren't aggregating across group names
         , MAX(g.name) AS name
    FROM user_group ug
    JOIN [group] g
        ON ug.group_id = g.ID
    WHERE user_id IN (1, 2, 3)
    GROUP BY ug.group_id
    --The distinct is only necessary if user_group
    --isn't keyed by group_id, user_id
    HAVING COUNT(DISTINCT user_id) = 3

SQL fiddle : http://sqlfiddle.com/#!6/0e968/3