如何计算单词在mysql数据库的多行中的出现 [英] how to count the occurence of a word in multiple rows of a mysql db

问题描述

我正在寻找一些帮助来统计mysql数据库中多行出现的次数.目的是创建像google这样的搜索引擎，但仅限于我的网站

i am looking for some help with counting the number of occurences in multiple rows of a mysql db.the purpose is for creting a search engine like google but only for my website

该方案是在最顶部显示单词出现次数最多的条目

the scheme is to display the entry which has the most number of occurences of the word at the very top

例如.用户搜索单词key.搜索记录并按发生次数的顺序显示数据

for eg . the user searches for the word key.the records are searched and the data is shown in order of the number of occurences

 1.this is a key which is a test key and also the profile key (3 occurences)
 2.key give me a key (2 occurences)
 ....and so on...

这是最好的方法吗?

SELECT (LENGTH(daya) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key') AS `count` ORDER BY `count` DESC // data is the column name and key is the word

还是有更好的

注意:我不想使用Like % %.我也想显示单词出现的次数，例如the word key has occured 3 times.我可以使用count()

Note:i dont want to use Like % %. also i want to show the number of times the word has occured like so the word key has occured 3 times.Could i use count()

推荐答案

您可以在ORDER BY子句中添加计数.

You can add the count in ORDER BY clause.

尝试这种方式:

SELECT *,(LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key') as cnt
FROM TableName
ORDER BY ((LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key')) DESC

要获取2次事件的记录，可以使用HAVING子句:

To get the records of 2 occurences, you can use HAVING clause:

SELECT *,(LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key') as cnt
FROM TableName
HAVING cnt=2
ORDER BY ((LENGTH(data) - LENGTH(REPLACE(data, 'key', ''))) / LENGTH('key')) DESC

这篇关于如何计算单词在mysql数据库的多行中的出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！