Skip to content

Latest commit

 

History

History
97 lines (73 loc) · 3.42 KB

File metadata and controls

97 lines (73 loc) · 3.42 KB
comments difficulty edit_url tags
true
中等
数据库

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

解法

方法一

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