MySQL 计数出现次数大于 2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3937283/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL count occurrences greater than 2
提问by fabjoa
I have the following table structure
我有以下表结构
+ id + word +
+------+--------+
The table gets filled with the words in lower cas of a given text, so the text
表格中充满了给定文本的较低 cas 中的单词，因此文本
Hello bye hello
你好再见你好
would result in
会导致
+ id + word +
+------+--------+
+ 1 + hello +
+------+--------+
+ 2 + bye +
+------+--------+
+ 3 + hello +
+------+--------+
I want to make a SELECT query that will return the number of words that get repeated at least two times in the table (like hello)
我想创建一个 SELECT 查询，该查询将返回表中至少重复两次的单词数（如 hello）
SELECT COUNT(id) FROM words WHERE (SELECT COUNT(words.word))>1
which of course is so wrong and super overloading when table is big. Any idea on how to achieve such purpose? In the given example inhere-above, I would expect 1
当桌子很大时，这当然是错误的和超载的。关于如何实现这样的目的的任何想法？在上面的给定示例中，我希望 1
回答by Mark Byers
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:
要获得出现多次的单词列表以及它们出现的频率，请使用 GROUP BY 和 HAVING 的组合：
SELECT word, COUNT(*) AS cnt
FROM words
GROUP BY word
HAVING cnt > 1
To find the number of words in the above result set, use that as a subquery and count the rows in an outer query:
要查找上述结果集中的单词数，请将其用作子查询并计算外部查询中的行数：
SELECT COUNT(*)
FROM
(
SELECT NULL
FROM words
GROUP BY word
HAVING COUNT(*) > 1
) T1
回答by bot403
SELECT count(word) as count
FROM words
GROUP BY word
HAVING count >= 2;
回答by a1ex07
SELECT word, COUNT(*) FROM words GROUP by word HAVING COUNT(*) > 1
SELECT word, COUNT(*) FROM words GROUP by word HAVING COUNT(*) > 1
