MySql:计算单词在列中出现的次数 [英] MySql: Count amount of times the words occur in a column

问题描述

例如，如果我在这样的列中有数据

For instance, if I have data in a column like this

data
I love book
I love apple
I love book
I hate apple
I hate apple

我如何获得这样的结果

I = 5
love = 3
hate = 2
book = 2
apple = 3

我们可以用MySQL实现吗?

Can we achieve this with MySQL?

推荐答案

以下是仅使用查询的解决方案:

Here is a solution only using a query:

SELECT SUM(total_count) as total, value
FROM (

SELECT count(*) AS total_count, REPLACE(REPLACE(REPLACE(x.value,'?',''),'.',''),'!','') as value
FROM (
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.sentence, ' ', n.n), ' ', -1) value
  FROM table_name t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(t.sentence) - LENGTH(REPLACE(t.sentence, ' ', '')))
 ORDER BY value

) AS x
GROUP BY x.value

) AS y
GROUP BY value

这是完整的工作提琴: http://sqlfiddle.com/#!2/17481a/1

Here is the full working fiddle: http://sqlfiddle.com/#!2/17481a/1

首先，我们进行查询以提取所有单词，如@apeterm所解释的此处(说明，如果您想自定义处理的单词总数).然后，我们将其转换为子查询，然后分别对每个单词的值进行COUNT和GROUP BY，然后在此基础上再次查询到GROUP BY未分组单词的情况，其中可能出现伴随符号.即:你好=你好！与REPLACE

First we do a query to extract all words as explained here by @peterm(follow his instructions if you want to customize the total number of words processed). Then we convert that into a sub-query and then we COUNT and GROUP BY the value of each word, and then make another query on top of that to GROUP BY not grouped words cases where accompanied signs might be present. ie: hello = hello! with a REPLACE

这篇关于MySql:计算单词在列中出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！