php - SQL 查询查找所有两个单词组合

Question

使用 PHP 和 MySQL，我需要从包含超过一百万行的表中查找按计数排序的所有“两个单词”组合。

搜索需要查找每个两个单词组合的出现次数，例如“hardware store”、“to the”、“second opportunity”、“for those”、“senior Citizen”等。

要搜索的文本示例:

id | content
1  | The senior citizens went to the hardware store from the community center.

2  | The hardware store is offering a second chance drawing for those senior citizens who did not win.

3  | Many senior citizens go to the town's community center to play bingo.

结果示例:

senior citizens - 3
to the - 2
hardware store - 2
community center - 2
second chance - 1
The senior - 1
center to - 1
the town's - 1
etc ...and so on.

结果需要包含所有“两个单词”组合。 “前辈”、“去了”、“硬件”、“市民去了”等，并统计找到了多少次。

我猜这可能是一个带有子查询的多查询解决方案，但我的查询构建专业知识很少。我尝试了一些基本查询，但我认为解决方案将比我的技能更复杂一些。

Similar question with different data source .

Answer 1

尝试 Union All 加入:

SELECT count(*) FROM your_table WHERE content LIKE '%senior citizens%'
UNION ALL
SELECT count(*) FROM your_table WHERE content LIKE '%to the%'
UNION ALL
SELECT count(*) FROM your_table WHERE content LIKE '%hardware store%'