Question

2738. 统计文本中单词的出现次数

English Version

题目描述

表：Files

+-------------+---------+
| 列名    | 类型  |
+-- ----------+---------+
| file_name   | varchar |
| content   | text  |
+-------------+---------+
file_name 为该表的主键（具有唯一值的列）。
每行包含 file_name 和该文件的内容。

 

编写解决方案，找出单词 'bull' 和 'bear' 作为 独立词 有出现的文件数量，不考虑任何它出现在两侧没有空格的情况（例如，'bullet', 'bears', 'bull.'，或者 'bear' 在句首或句尾 不会 被考虑）。

返回单词 'bull' 和 'bear' 以及它们对应的出现文件数量，顺序没有限制 。

结果的格式如下所示：

 

示例 1：

输入：
Files 表:
+------------+----------------------------------------------------------------------------------+
| file_name  | contenet                                     | 
+------------+----------------------------------------------------------------------------------+
| draft1.txt | The stock exchange predicts a bull market which would make many investors happy. | 
| draft2.txt | The stock exchange predicts a bull market which would make many investors happy, |
|        | but analysts warn of possibility of too much optimism and that in fact we are  |
|        | awaiting a bear market.                              | 
| draft3.txt | The stock exchange predicts a bull market which would make many investors happy, |
|        | but analysts warn of possibility of too much optimism and that in fact we are  |
|        | awaiting a bear market. As always predicting the future market is an uncertain   |
|      | game and all investors should follow their instincts and best practices.     | 
+------------+----------------------------------------------------------------------------------+
输出： 
+------+-------+
| word | count |  
+------+-------+
| bull | 3   | 
| bear | 2   | 
+------+-------+
解释：
- 单词 "bull" 在 "draft1.txt" 中出现1次，在 "draft2.txt" 中出现 1 次，在 "draft3.txt" 中出现 1 次。因此，单词 "bull" 出现在 3 个文件中。
- 单词 "bear" 在 "draft2.txt" 中出现1次，在 "draft3.txt" 中出现 1 次。因此，单词 "bear" 出现在 2 个文件中。

解法

方法一

# Write your MySQL query statement below
SELECT 'bull' AS word, COUNT(*) AS count
FROM Files
WHERE content LIKE '% bull %'
UNION
SELECT 'bear' AS word, COUNT(*) AS count
FROM Files
WHERE content LIKE '% bear %';