根据您的示例查询，您似乎只对特定用户感兴趣。这是基于该外观。
使用HAVING子句。此外，由于您希望查询用户ID，因此您可以INNER JOIN（不是左联接）到user_chat。

create table chats (
  id integer, 
  chat_name varchar(20), 
  is_group_chat varchar(10), 
  create_at date
  );
  
insert into chats values 
(1, 'fruit', 'false', '2023-10-01'), 
(2, 'fruit', 'false', '2023-10-02'), 
(3, 'cars', 'true', '2023-10-03');

create table user_chats (
  chat_id integer, 
  user_id integer
  );
  
insert into user_chats values 
(1, 1), 
(1, 2), 
(2, 1), 
(2, 3), 
(3, 4), 
(3, 2);

个字符
| 聊天标识|聊天名称|创建于|
| --|--|--|
| 1 |水果|2023-10-01T00：00：00.000Z|
View on DB Fiddle

您可以简单地连接表，按聊天id对行进行分组，然后对它们进行计数。

WITH TwoUserChats AS (
    SELECT c.id as chat_id
    FROM chats c INNER JOIN
        user_chats uc ON uc.chat_id = c.id 
    WHERE c.is_group_chat = false 
        AND c.id IN 
          (
            '39438219-1488-4e6f-820a-2ba16ba12e01', 
            '16c1beb9-687a-45b3-8d2b-88c71a681168'
          )
    GROUP BY c.id
    HAVING COUNT(*) = 2
)

SELECT DISTINCT
     c.id AS c_id  
    ,c.chat_name AS chat_name 
    ,c.created_at AS created_at
FROM chats c INNER JOIN 
    TwoPersonChats ON c.id = TwoPersonChats.ID

字符串

如果你想包含涉及其他用户的聊天，只需删除count()过滤器：

select * from chats where is_group_chat = 'false' and chat_id in (
    select chat_id
    from user_chat
    group by chat_id
    having min(chat_id) = 'X' and max(chat_id) = 'Y' and count(chat_id) = 2
);

字符串
如果您可能需要检查两个以上的用户ID（或者如果您只想使用具有两个ID的版本）：

select * from chats where is_group_chat = 'false' and chat_id in (
    select chat_id
    from user_chat
    group by chat_id
    having count(case when chat_id in ('X', 'Y', ...) then 1 end) = N
);

型