现有需求:查询card_info表的不同告警类型所占的百分比,字段warn_type部分记录含有多个值,需要进行拆分统计,一行拆分为多行
参考链接:https://blog.csdn.net/qq327563603/article/details/80583326#commentBox
select a.warn_id,substring_index(substring_index(a.warn_type,',',b.help_topic_id+1),',',-1) as warn_type
from warn_info a join mysql.help_topic b on b.help_topic_id < (length(a.warn_type) - length(replace(a.warn_type,',',''))+1)
运行结果:
转化后进行分组统计数量
select warninfos.warn_type,COUNT(warninfos.warn_type)
from (select a.warn_id,substring_index(substring_index(a.warn_type,',',b.help_topic_id+1),',',-1) as warn_type
from warn_info a join mysql.help_topic b on b.help_topic_id < (length(a.warn_type) - length(replace(a.warn_type,',',''))+1)
) warninfos
GROUP BY warninfos.warn_type
Ok~~~~~