找到双方都参与的聊天记录

我想你可能没有补充存在条件的地方。

 SELECT DISTINCT "t1".chat_id
FROM "chat_person" t1
WHERE 
    EXISTS (
        SELECT 1 FROM "chat_person" t2
        WHERE "t2".person_id = 20 and t2.ChatID = "t1".chat_id  
    )
    AND "t1".person_id = 2

最简单的方法是聚合:

select chat_id
from chat_person
group by chat_id
having bool_or(person_id = 2) and bool_or(person_id = 20);

如果您需要所有其他字段,可以尝试下面的方法

select t1.* from chat_person t1
 where exists ( select 1 from chat_person t2 where t2.chat_id=t1.chat_id
                                and person_id in (2,20)
                               having count(distinct person_id)=2)

chat_id

   select chat_id from cte t2 where 
   person_id in (2,20)
   group by chat_id
   having count(distinct person_id)=2

demo

SELECT chat_id, count(distinct 
person_id) from table 
group by chat_id having 
  count(case when person_id in (2,20)
 then person_id end)=2