在Mysql中如何进行这个查询呢？

Question

我有 3 个表：消息、主题和 message_subject_rel。 这个想法是拥有可以与许多主题相关的消息，然后进行跨主题搜索。

假设我有一条消息：

Id: 1, Message: This is a message

2 个主题：

Id:1, Subject: Math
Id:2, Subject: Science

并且有 2 个 message_subject_rel 条目：

Id: 1, message_id: 1, subject_id: 1
Id: 2, message_id: 1, subject_id: 2

如果我想搜索与数学相关的消息，我会与 3 个表和where 子句将是 subject = "Math"

但我不知道该怎么做，是搜索与数学和科学相关的消息。 如果我做一个简单的连接，我会得到类似这样的表格：

id  message     user_id     created_at  ip  id  message_id  subject_id  id  subject

如果我做一个 where subject = "Math" and subject = "Science" 我不会得到任何结果，因为每条消息只有 1 个主题每行相关，但主题超过 1 的消息有重复行。

那么，你有什么推荐呢？

Answer 1

这与 Filtering from join-table 基本上是相同的问题，

我将调整我的答案这个问题。

JOIN 解决方案：

SELECT m.*
FROM messages m
 JOIN message_subject_rel ms1 ON (m.id = ms1.message_id)
 JOIN subjects s1 ON (ms1.subject_id = s1.id AND s1.subject = 'Math')
 JOIN message_subject_rel ms2 ON (m.id = ms1.message_id)
 JOIN subjects s2 ON (ms2.subject_id = s2.id AND s2.subject = 'Science');

GROUP BY 解决方案：

请注意，除非您使用 MySQL，否则您需要在 GROUP BY 子句中列出所有 m.* 列。

SELECT m.*
FROM messages m 
 JOIN message_subject_rel ms ON (m.id = ms.message_id)
 JOIN subjects s ON (ms.subject_id = s.id)
WHERE s.subject IN ('Math', 'Science'))
GROUP BY m.id, ...
HAVING COUNT(*) = 2;

子查询解决方案：

SELECT m.*
FROM messages m
WHERE m.id = ANY (SELECT message_id 
                  FROM message_subject_rel ms JOIN subjects s 
                    ON (ms.subject_id = s.id) 
                  WHERE s.subject = 'Math')
  AND m.id = ANY (SELECT message_id 
                  FROM message_subject_rel ms JOIN subjects s 
                    ON (ms.subject_id = s.id) 
                  WHERE s.subject = 'Science');

修改的 GROUP BY 解决方案：

通过隔离子查询中的搜索来简化 GROUP BY 子句。

SELECT m.*
FROM messages m
WHERE m.id IN (
  SELECT ms.message_id FROM message_subject_rel ms JOIN subjects s
    ON (ms.subject_id = s.id)
  WHERE s.subject IN ('Math', 'Science'))
  GROUP BY ms.message_id HAVING COUNT(*) = 2
);

PS：您的 message_subject_rel 表没有理由需要 ID 列。

Answer 2

在 where 子句中进行内部选择。

SELECT FROM [表和连接] WHERE subject = "Math" AND message_id IN (SELECT message_id FROM [表和连接] WHERE subject = "Science")