mysql - 查询以获取数据库表中给定单词的出现次数

Question

我在数据库中有以下表格。

users-----------------| id | username |-----------------|  1 | goje     ||  2 | john     ||  3 | henry    |-----------------comments-------------------------------------------------| id | uid |          comment                   |-------------------------------------------------|  1 |  1  | One who works hard is the one who  ||    |     | succeeds                           ||  2 |  1  | This one was the best.             ||  3 |  2  | You are one of my best friends.    ||  4 |  3  | He was the one who bought this one.|-------------------------------------------------

现在，假设给定的单词是“one”。我想要一个 MySQL 查询，可以让我知道用户表中每个用户这个词的出现次数。在这种情况下，结果应该是

--------------------| username | count |--------------------| goje     | 3     |   -> 2 in first comment and 1 in second| henry    | 2     |   -> 2 in henry's only comment| john     | 1     | --------------------

请告诉我如何使用 SQL 实现此目的。谢谢!!

Answer 1

试试这个查询:

SELECT
 users.username AS username,
 round(sum((LENGTH(comments.comment) - LENGTH(REPLACE(LOWER(comments.comment),"one", ""))) / LENGTH("one")), 0) AS count
FROM users
 INNER JOIN comments ON comments.uid = users.id
GROUP BY users.id
ORDER BY count DESC;

我不知道 REGEXP 方法是更便宜还是更昂贵。