选择按列总和排序的两个查询的交集

Question

我有三张表，如下所示：

documents (id, content) 
words (id, word) 
word_document (word_id, document_id, count)

Words table（单词表）包含了所有文件中出现过的单词，word_document（单词文档）将一个单词与一个文档相关联，并记录该单词在该文档中的出现次数。

我想编写一个查询来搜索两个单词，并仅返回同时拥有这两个单词的文档，按照这两个单词在文档中的计数之和排序。

例如

DocA: green apple is not blue
DocB: blue apple is blue
DocC: red apple is red

现在搜索苹果和蓝色将返回以下结果：

DocA, 3
DocB, 2

因为:

DocA contains both words and 3 of them
DocB contains both words and 2 of them
DocC only contains one word

我成功使用了intersect，但是它并没有返回总数和顺序。

- Ali

Answer 1

我想这应该可以解决问题：

select a.document_id, a.count + b.count
from 
(
 select document_id, count
 from word_document
 where word_id = 'apple'
 group by document_id
) a 
INNER JOIN 
(
 select document_id, count
 from word_document
 where word_id = 'blue'
 group by document_id
) b 
ON a.document_id = b.document_id
ORDER BY a.count + b.count

- StevieG

---

Mahmoud的答案对我有用，我不知道哪个性能更好。但我认为他的更好，因为它只查询了word_document一次！ - Ali

Answer 2

对于那些想要这个的人，这只有一个作用：

select wd.document_id, (wd.count + d.count) as tcount from word_document as wd
join words as w on w.id = wd.word_id
join
(select document_id, count from word_document 
join words on words.id = word_document.word_id
where words.word = "apple") d on d.document_id=wd.document_id
where w.word = "blue" order by tcount desc

你可以从内部查询创建临时表，并在其上执行外部查询。这可以递归地进行，以处理更多的单词。

- Ali