MySQL 获取比 `x` 更频繁出现的不同字段数

Question

我有两个表，一个word 表和一个document 表。下面的查询用于获取单词列表以及每个单词在特定语言的所有文档中出现的次数。

select body, count(*) as count from word
join document on word.document_id = document.id
where document.language = 'english'
group by body
order by count(*) desc

它产生了这样的预期结果:

body    count
-------------
and     44
the     42
a       28
in      24

如何获得比任何给定单词更常见的单词数？给定单词 in，我如何查询以获得 3，因为在我的文档中三个单词比 in 更常见？

Answer 1

试试这个:

SELECT @rank:=@rank, body, counter
FROM
(
SELECT body, COUNT(*) AS counter
FROM word
JOIN document ON word.document_id = document.id
WHERE document.language = 'english'
GROUP BY body
ORDER BY counter desc
) t, (SELECT @rank := 0) r;

编辑:如果您想获得比 ex.'in' 更高的字数

SELECT @rank:=@rank AS rank
FROM
(
SELECT body, COUNT(*) AS counter
FROM word
JOIN document ON word.document_id = document.id
WHERE document.language = 'english' and body = 'in'
GROUP BY body
ORDER BY counter desc
) t, (SELECT @rank := 0) r;