mysql - 获取两个用户之间的相互订阅

Question

我有一个表格如下

id comm_id user_id ..

这存储用户对社区的订阅。现在我想查询这个表，以便只获取 2 个用户之间相互的订阅(比如 user_id:2 和 9)

给我sql查询表名:db_community_subscribersid, community_id, user_id

"20"      1         "2"
"28",  NULL         "2"
"31"   NULL,        "2"
"43",   4           "2"
"47     1             9
"57",   NULL,       "2"
"59",   "12",      "9"
"60     14          2
"62",  NULL         2

这些是用户 2 和 9 的订阅

我只想选择 1 和 12，因为它们是常见的订阅

Answer 1

试试这个:

我认为这现在可以工作了:

SELECT Comm_ID
FROM tableNAme
WHERE  user_id IN (2,9)
GROUP BY Comm_ID
HAVING COUNT(Comm_ID) > 1

或

SELECT DISTINCT d.Comm_ID
FROM
    (SELECT Comm_ID
    FROM tableNAme
    WHERE user_id in (1,2)) d
GROUP BY d.Comm_ID
HAVING COUNT(d.Comm_ID) > 1