mysql - 计算一行包含关键字的频率

Question

计算每个关键字在多少行中出现的最有效方法是什么。例如给定以下列表:

cat, dog, basement, parrot, whale, dolphins

并给定以下简单数据库:

ID  TEXTDATA
1   The cat is in the basement, the cat is not on the roof.
2   The dog is going home.
3   The birds are flying in the sky.
4   The dolphins are swimming in the ocean, i really like dolphins.
5   The mouse live in the basement.

结果应该是:

cat=1 ( two occurrences in one row )
dog=1 ( one occurrence in one row )
basement=2 ( two occurrences in two rows )
parrot=0 ( zero occurrences )
whale=0 ( zero occurrences )
dolphins=1 ( two occurrence in one row )

结果可以在 coldfusion 或 mysql 中计算，以更高效的为准。

Answer 1

尝试嵌套循环。

<cfset wordCount = ArrayNew(2)>
<cfset arrayRow = 0>
<cfset words = "cat,dog,etc">
<cfloop list = "#words# index = "thisWord">
<cfset arrayRow ++>
<cfset wordCount[arrayRow][1] = thisWord>
<cfset wordCount[arrayRow[2] = 0>

<cfloop query = "yourQuery">
<cfif listfind(textdata, thisWord, " "> <!--- space delimited --->
<cfset wordCount[arrayRow[2] += 1>
closing tags