【mysql】将逗号分割的字段内容转换为多行并group by

先说需求：
公司想让我通过mysql导出一个报表，内容为公司每个人参加会议的次数，现在有一个会议表fusion_meeting，正常的逻辑是通过人员直接group by就可以得出结果，但是我们的参会人是通过逗号分割这种方式存在一个字段里，这就导致无法直接group by。
所以我们要通过将逗号分割的字段内容转换为多行然后再group by

1、原来的字段格式

在这里插入图片描述

2、将逗号分割的字段内容转换为多行

下面直接给出sql，并对sql的每一步做出解释，更有助于大家理解

首先要说明的是，mysql.help_topic本身是mysql的一张信息表,用来存储各种注释等帮助信息，help_topic拥有一个自增为1的id属性–help_topic_id ，并且可以当做下标来使用，拥有固定数量的数据

解释：

length(a.attendee_uid) - length(REPLACE(a.attendee_uid, ‘,’, ‘’)) + 1
第一步的意思是字段attendee_uid的长度 - 字段attendee_uid去除掉逗号的长度，然后再+1就得到了通过逗号分割后有几条数据
比如上一步得到是3 那就可以确定这个字段要拆分为3行 help_topic_id<3 也就是可以得到下标 0,1,2
比如这条数据’zhangsan,lisi,wangwu’ 第一个substring_index的意思就是把’zhangsan,lisi,wangwu’通过逗号分割，然后取b.help_topic_id + 1（help_topic_id就是第3步得到的下标）结果就是zhangsan
第二个substring_index的意思是再从第4步的结果从右边取第一个，因为’zhangsan,lisi,wangwu’如果获取到下标为2的话那得到的就是’zhangsan,lisi’ 所以再从右边取第一个就得到了 ‘lisi’

SELECT 
	a.id '会议id', 
	a.attendee_uid '原始参会人列表', 
	# 4、比如这条数据'zhangsan,lisi,wangwu'  第一个substring_index的意思就是把'zhangsan,lisi,wangwu'通过逗号分割，
	#    然后取b.help_topic_id + 1（help_topic_id就是第3步得到的下标）结果就是zhangsan
	# 5 第二个substring_index的意思是 再从第4步的结果 从右边取第一个， 因为'zhangsan,lisi,wangwu'如果获取到下标为2的话那得到的就是'zhangsan,lisi'  所以再从右边取第一个就得到了 'lisi'
	substring_index(substring_index(a.attendee_uid, ',', b.help_topic_id + 1), ',', -1) AS '分割后的参会人账号' 
FROM `fusion_meeting` a 
JOIN mysql.help_topic b 
# 1、length(a.attendee_uid) - length(REPLACE(a.attendee_uid, ',', '')) + 1 
# 2、这个的意思是 字段attendee_uid的长度 - 字段attendee_uid去除掉逗号的长度，然后再+1就得到了通过逗号分割后有几条数据
# 3、比如上一步得到是3  那就可以确定这个字段要拆分为3行 help_topic_id<3 也就是可以得到下标 0,1,2
ON b.help_topic_id < length(a.attendee_uid) - length(REPLACE(a.attendee_uid, ',', '')) + 1
WHERE a.hw_conf_id = '969471016';

结果：
在这里插入图片描述

3、对以上结果进行分组

SELECT uid as '参会人账号',COUNT(*) '参会次数' FROM (
SELECT 
	a.id '会议id', 
	a.attendee_uid '原始参会人列表', 
	substring_index(substring_index(a.attendee_uid, ',', b.help_topic_id + 1), ',', -1) AS uid 
FROM `fusion_meeting` a 
JOIN mysql.help_topic b 
ON b.help_topic_id < length(a.attendee_uid) - length(REPLACE(a.attendee_uid, ',', '')) + 1
WHERE a.hw_conf_id = '969471016'

) c GROUP BY c.uid;

结果：
在这里插入图片描述

文章持续更新，可以关注下方公众号或者微信搜一搜「最后一支迷迭香」第一时间阅读，获取更完整的链路资料。