创新路
我们一直在努力

MySQL学习笔记(7)

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

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

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)

客官点个赞呗! (0)
分享到:

评论 抢沙发

评论前必须登录!

天府云博 - 做有态度的开发&运维&设计学习分享平台!

联系我们百度云主机