mysql计算单词的个数_如何在MySQL字符串中提取第n个单词并计算单词出现次数？...

WITH cte AS (

SELECT digits.idx,

SUBSTRING_INDEX(SUBSTRING_INDEX(words, '~', digits.idx + 1), '~', -1) word

FROM

(SELECT reg_replace(UPPER(txt),

'[^''’a-zA-Z-]+',

'~',

TRUE,

1,

0) AS words

FROM tbl) delimited

INNER JOIN

(SELECT @row := @row + 1 as idx FROM

(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,

(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,

(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3,

(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4,

(SELECT @row := -1) t5) digits

ON LENGTH(REPLACE(words, '~' , '')) <= LENGTH(words) - digits.idx)

SELECT c.word,

subq.occurrences

FROM cte c

LEFT JOIN (

SELECT word,

COUNT(*) AS occurrences

FROM cte

GROUP BY word

) subq

ON c.word = subq.word

WHERE idx = 1; /* idx is zero-based so 1 here gets the second word */