建構 3 步 sql 查詢

我有：

• Characters帶有一character_id列的表格；
• Chats帶有character_id和conversation_id列的連接表；
• 和一個Conversations帶有一conversation_id列的表格。

我正在嘗試建構一個查詢，以查找兩個字元（sender和recipient）之間的對話，並且僅查找這兩個字元（其他對話可能與發件人、收件人和其他涉及的角色存在 - 組對話）。

我正在使用Ruby on Rails。

查詢涉及三個步驟：

1. 查找屬於發件人的所有對話。
2. 從這些中選擇也屬於收件人的。
3. 從這些中選擇僅屬於發件人和收件人的一個。

我無法完全正確地查詢。我管理過的最好的如下。

我哪裡錯了？

下面的查詢給出了這個錯誤：PG::SyntaxError: ERROR: syntax error at or near "GROUP"

SELECT Conversations.id FROM
(
   (
       (
           Conversations INNER JOIN ( SELECT conversation_id FROM Chats WHERE Chats.character_id=#{sender_id} ) AS sterling
                         ON Chats.conversation_id=Conversations.id
       )
       INNER JOIN ( SELECT conversation_id FROM Chats WHERE Chats.character_id=#{recipient_id} ) AS archer
       ON Chats.conversation_id=Conversations.id
   )
   GROUP BY conversation_id
   HAVING COUNT(Chats.conversation_id)=2
);

為什麼不直接使用：

SELECT id FROM Conversations
INNER JOIN (
   SELECT conversation_id FROM Chats  WHERE Chats.character_id= '#{sender_id}' 
) AS sterling
   ON sterling.conversation_id = Conversations.id
INNER JOIN ( 
   SELECT conversation_id FROM Chats WHERE Chats.character_id = '#{recipient_id}' 
) AS archer
   ON archer.conversation_id = Conversations.id;

由於這些是內部連接，因此它必須同時存在於發件人和收件人中。它只會返回三個表中的對話。

計數在這裡似乎沒有用，除非您試圖讓人們一起進行超過 1 次對話。

它也應該適用於這個更簡單的版本，儘管它不檢查 in 的conversation_id存在Conversations：

SELECT conversation_id 
FROM Chats AS sterling
INNER JOIN  Chats AS archer
   ON sterling.conversation_id = archer.conversation_id
WHERE sterling.character_id = '#{recipient_id}' 
   AND archer.character_id = '#{sender_id}';

退出計數：

SELECT cv.conversation_id
FROM (
   SELECT conversation_id 
   FROM Chats AS sterling
   INNER JOIN  Chats AS archer
       ON sterling.conversation_id = archer.conversation_id
   WHERE sterling.character_id = '#{recipient_id}' 
   GROUP BY conversation_id 
   HAVING count(distinct character_id) = 2
) ce
INNER JOIN  Chats AS ch
       ON ce.conversation_id = ch.conversation_id
   WHERE ch.character_id = '#{sender_id}';

引用自：https://dba.stackexchange.com/questions/120609