MySQL count occurrences greater than 2

我有下面的表结构

+  id  +  word  +
+------+--------+

表格中的单词以给定文本的小写形式填充，因此文本

你好，拜拜，你好

会导致

+  id  +  word  +
+------+--------+
+   1  + hello  +
+------+--------+
+   2  + bye    +
+------+--------+
+   3  + hello  +
+------+--------+

我想做一个 SELECT 查询，它将返回在表中至少重复两次的单词数(比如 hello)

SELECT COUNT(id) FROM words WHERE (SELECT COUNT(words.word))>1

当然这是错误的，当表很大的时候超载。对于如何达到这个目的有什么想法吗？在上面给出的例子中，我期望1

168147 次浏览

小开

最佳答案

To get a list of the words that appear more than once together with how often they occur, use a combination of GROUP BY and HAVING:

SELECT word, COUNT(*) AS cnt
FROM words
GROUP BY word
HAVING cnt > 1

若要查找上述结果集中的单词数，请将其用作子查询，并对外部查询中的行进行计数:

SELECT COUNT(*)
FROM
(
SELECT NULL
FROM words
GROUP BY word
HAVING COUNT(*) > 1
) T1

小开

SELECT count(word) as count
FROM words
GROUP BY word
HAVING count >= 2;

小开

SELECT word, COUNT(*) FROM words GROUP by word HAVING COUNT(*) > 1

小开

The HAVING option can be used for this purpose and query should be

SELECT word, COUNT(*) FROM words
GROUP BY word
HAVING COUNT(*) > 1;