@Elad Meidar,我喜欢你的问题,我找到了一个解决方案:
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
首先,我们执行一个查询来提取所有单词,详见@peterm在
这里的解释(如果您想自定义要处理的单词总数,请按照他的指示操作)。然后,我们将其转换为子查询,然后使用
COUNT
和
GROUP BY
对每个单词的值进行分组计数,然后在其上再次进行查询,以
GROUP BY
未分组的单词,其中可能存在附带符号的情况。例如:hello = hello!应用了
REPLACE
函数。