将一列以逗号分隔的值行转列
有时会遇到没有遵守第一范式设计模式的业务表。即一列中存储了多个属性值,如下:
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)