根据单词在数据库中的出现对单词进行排序，匹配相似的单词

Question

null

Answer 1

您需要使用GROUP BY子句和count函数。

SELECT word, COUNT(*) count FROM your_table GROUP BY word ORDER BY count DESC LIMIT 0,3;

可能的输出：

+------+-------+
| word | count |
+------+-------+
| hai  | 2     |
| Hai. | 1     |
| This | 1     |
+------+-------+

如果您希望MySQL将hai和hai.视为同一个单词，则应该在分组之前去掉所有非alpha字符。参见MySQL：如何从字符串中删除所有非字母数字字符？。然后，基于此答案中的方法，它将如下所示：

SELECT LOWER(alphanum(word)) word, COUNT(*) count FROM your_table 
GROUP BY LOWER(alphanum(word)) ORDER BY count DESC LIMIT 0,3;

+------+-------+
| word | count |
+------+-------+
| hai  | 3     |
| this | 1     |
| joe  | 1     |
+------+-------+