mysql - SQL 通过对行进行分组获取公共(public)值

Question

假设我有一个这样的表:

| uid | group_id | 
-----------------
|   1 |     1    |
|   1 |     2    |
|   1 |     3    |
|   1 |     4    |
|   2 |     1    |
|   2 |     2    |
|   2 |     3    |
|   3 |     1    |
|   3 |     2    |
|   3 |     4    |
...

如何为一些 uids 获取通用的 group_ids 集。

例如
对于 uid 1,2 :常见的 group_ids 是 1,2,3
对于 uid 1,2,3 :常见的 group_ids 是 1 和 2

Answer 1

假设每个 group_ID 对于每个 uid 都是唯一的，(否则您需要使用 distinct)

SELECT group_ID
FROM tableName
WHERE uid IN (1,2,3)
GROUP BY group_ID
HAVING COUNT(*) = 3

• SQLFiddle Demo (includes two queries)