postgeSql 多对多搜索

SELECT DISTINCT ch1.ChatID 
FROM chatsUsers ch1
JOIN chatsUsers ch2 on ch1.ChatID = ch2.ChatID
WHERE 
    ch1.UserID in (1, 25) and ch.UserID in (1, 25) and ch1.UserID <> ch2.UserID 

JOIN使用阵列 的一欧姆替代方案：

SELECT chat_id
  FROM chats
       LEFT JOIN chatsusers USING (chat_id)
 GROUP BY chat_id
HAVING COUNT(user_id) = 2
   AND ARRAY_AGG(user_id) @> ARRAY[1, 25]
   AND ARRAY_AGG(user_id) <@ ARRAY[1, 25]
;

DB小提琴： https ://www.db-fiddle.com/f/fSRSBg6Bdeer4LBd3bdMpQ/0 。

select
   ChatId
from chatsUsers
group by ChatId
having count(*) = 2