将一列以逗号分隔的值行转列

有时会遇到没有遵守第一范式设计模式的业务表。即一列中存储了多个属性值，如下：

pk  value

1    ET,AT

2    AT,BT

3    AT,DT

4    DT,CT,AT

1、得到所有的不重复的值，如

value

AT

BT

CT

DT

ET

实现思路：

1. 通过group_concat函数将value列的值拼接成一个逗号隔开的字符串，然后通过substring_index函数对字符串进行截取

2. 通过substring_index函数特性，我们就需要知道字符串有多少个逗号，并且要告诉每个逗号的位置

3. 逗号个数=char_length(字符串)-char_length(replace(字符串,’,’,’’))

4. 逗号位置=mysql.help_topic.id < 逗号个数[+1]

5. 最后通过distinct函数将截取后的单个值进行去重

Select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1))

from (select group_concat(distinct `value`) as col from `row_to_col`) as a

join mysql.help_topic as b on b.help_topic_id < (char_length(a.col) – char_length(replace(a.col,',',''))+1)

2、显示每个值及其对应主键，如

pk  value

1    ET

1    AT

2    AT

2    BT

3    AT

3    DT

4    DT

4    CT

4    AT

实现思路：

思路基本与1同，只是最后的查询不一样

涉及到的函数：

length:返回字符串所占的字节数，是计算字段的长度。一个汉字或是中文符号是算三个字符,一个数字或字母或英文符号算一个字符。

char_length:返回字符串所占的字符数，不管汉字还是数字或者是字母或者符号(不分中英文)都算是一个字符。

replace(str,old_string,new_string): 将字符串中str中所有的old_string替换成new_string。

substring_index(被截取字段，关键字，关键字出现的次数)：截取字符串。如果关键字出现的次数是负数 ， 则是从后倒数，到字符串结束。

group_concat([DISTINCT] 要连接的字段(可多个,逗号隔开) [Order BY 排序字段 ASC/DESC] [Separator '分隔符'])：

将group by产生的同一个分组中的值连接起来，返回一个字符串结果。分隔符，默认逗号。

select a.pk,substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)

from (select `value` as col,pk from `row_to_col`) as a join

 mysql.help_topic as b on b.help_topic_id < (char_length(a.col) – char_length(replace(a.col,',',''))+1)

未经允许不得转载：天府数据港官方信息博客 » MySQL学习笔记（7）