一个字段存以逗号分隔数据，要以这个字段做统计

有个二次需求，不想再加表，一个字段存以逗号分隔数据，要以这个字段做统计，加一张表有点娄
所以写了一个sql解决

先写个小例子：
实现的效果：需要将一行数据变成多行
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(‘7654,7698,7782,7788’,’,’,help_topic_id+1),’,’,-1) AS num
FROM
mysql.help_topic
WHERE
help_topic_id < LENGTH(‘7654,7698,7782,7788’)-LENGTH(REPLACE(‘7654,7698,7782,7788’,’,’,’’))+1;

设计思路
逻辑并不困难,关联查询而已,至于分割字符串的,我也在网上找到了参考写法
参考了这篇博客:
https://blog.csdn.net/pjymyself/article/details/81668157

select count(1) as count,aa.qualifications from
(SELECT
SUBSTRING_INDEX( SUBSTRING_INDEX( a.qualifications, ‘,’, help_topic_id + 1 ), ‘,’,- 1 ) AS qualifications
FROM
mysql.help_topic m,
biz_personnel_files a
WHERE
help_topic_id < LENGTH( a.qualifications ) - LENGTH( REPLACE ( a.qualifications, ‘,’, ‘’ ) ) + 1
and a.is_job = ‘0’
) as aa
WHERE
aa.qualifications IS NOT NULL
GROUP BY aa.qualifications ORDER BY aa.qualifications;