SQL拆分列中的元素为每一行

问题

原始数据

ods_tc_employee_info表

number			like_color
20210816101443	蓝
20140714100005	蓝
20200323100615	蓝,紫,粉
20160307100034	蓝,黑
20210406101195	蓝
20200401100624	白,蓝,绿
20210707101377	黑,白,
20191107100518	五彩斑斓的黑
20210317101041	蓝
20210415101215	红,蓝
20210322101157	蓝
20210825101454	灰,黄
20210804101431	红
20210401101185	蓝
20210401101186	黑
20190902100464	黑,白,灰
20200915100929	蓝,黑
20210506101255	黑

现在需要将like_color列进行拆分，希望得到如下形式的表，以方便统计。

number			color
20140714100005	蓝
20160307100034	蓝
20160307100034	黑
20190902100464	黑
20190902100464	白
20190902100464	灰

这里需要创建一张辅助表，该表需要有一列连续的数列，在此我直接使用MySQL中自带的help_topic_id表，该表如下所示：

image-20220125165844819

其中help_topic_id列为一个自增的连续数列，最大值为658，基本够用。

这个值一定要大于原始数据中要分割的值的个数。

结论

SELECT otei.`number` , substring_index(substring_index(otei.like_color, ',', ht.help_topic_id + 1), ',' ,-1) as color 
FROM ods_tc_employee_info otei 
JOIN mysql.help_topic ht 
ON ht.help_topic_id < (length(otei.like_color) - length(replace(otei.like_color,',',''))+1)
ORDER BY otei.`number` ;

得到结果：

image-20220125170429906

目的达成💯

解析

其实是原理是用原始数据表与辅助表进行笛卡尔积运算。

length()函数用于求该列元素的长度

length(replace(otei.like_color,',','')用于求该列元素去除分隔符（,）后的长度

length(otei.like_color) - length(replace(otei.like_color,',',''))+1即：元素长度 - 去除分隔符后的元素长度 + 1 = 分割后的元素个数

image-20220125171642464

substring_index()函数用于取某个字符分割后的子串

substring_index(otei.like_color, ',', ht.help_topic_id + 1)的意思是like_color列按,分割，后的第ht.help_topic_id + 1个元素，效果如下：

image-20220125180514421

因此，还需要再使用substring_index()取一次最后一个元素，即：substring_index(substring_index(otei.like_color, ',', ht.help_topic_id + 1), ',' ,-1)

最终过程翻译成伪代码如下：

for (i = 0; i < 分割后的元素个数; i++) {
	获取最靠近第 i 个逗号之前的数据
}