mysql - 如何在 MySQL 中找到最流行的单词出现次数？

Question

我有一个名为 results 的表格，有 5 列。

我想使用 title 列查找以下行:WHERE title like '%for sale%'，然后列出最流行的单词那一栏。一个是 for，另一个是 sale，但我想看看其他词与此相关。

样本数据:

title
cheap cars for sale
house for sale
cats and dogs for sale
iphones and androids for sale
cheap phones for sale
house furniture for sale

结果(单字):

for    6
sale    6
cheap    2
and    2
house    2
furniture 1
cars    1
etc...

Answer 1

您可以通过一些字符串操作来提取单词。假设您有一个数字表并且单词由单个空格分隔:

select substring_index(substring_index(r.title, ' ', n.n), ' ', -1) as word,
       count(*)
from results r join
     numbers n
     on n.n <= length(title) - length(replace(title, ' ', '')) + 1
group by word;

如果您没有数字表，您可以使用子查询手动构建一个:

from results r join
     (select 1 as n union all select 2 union all select 3 union all . . .
     ) n
     . . .

SQL Fiddle(由@GrzegorzAdamKowalski 提供)是here .