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

题目描述

表：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 个文件中。

解法

方法一

MySQL

# 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 %';

评论