mysql计算单词的个数,MySql：计算单词在列中出现的次数

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

How can I get result like this

I = 5

love = 3

hate = 2

book = 2

apple = 3

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

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