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

Question

我有一个表，如下所示

id comm_id user_id ..

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

给我sql查询表名: db_community_subscribers id，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

Answer 2

请在下面尝试

  SELECT group_concat(distinct comm_id)
  FROM table 
  WHERE user_id in(2, 9)
  group by user_id  having count(id) >1

或者：

  SELECT distinct comm_id
  FROM table 
  WHERE user_id in(2, 9)
  group by user_id  having count(id) >1

请做必要的更改，让我知道如果你还想要什么。

Answer 3

尝试：

select comm_id, group_concat(id) as subscription_ids
from subscriptions
where user_id in (2,9)
group by comm_id
having count(distinct user_id)=2