mysql 拆分字符串,并count出字符串的长度
遇到
图中需要将按uid分组count出每一个word_id个数的情况,需要用到substring_index函数
例子代码如下:
SELECT
COUNT(c.wid) as wcount,
c.uid
FROM
(
SELECT
a.uid AS uid,
substring_index(
substring_index(
a.word_id,
',',
b.help_topic_id + 1
),
',' ,- 1
) AS wid
FROM
word_exam_passed a
JOIN mysql.help_topic b ON b.help_topic_id < (
length(a.word_id) - length(REPLACE(a.word_id, ',', '')) + 1
)
) c
where c.uid in (1,7003)
GROUP BY
c.uid ;