MySQL中将一列以逗号分隔的值行转列的实现中将一列以逗号分隔的值行转列的实现
前言前言
有时会遇到没有遵守第一范式设计模式的业务表。即一列中存储了多个属性值。如下表
pk value
1 ET,AT
2 AT,BT
3 AT,DT
4 DT,CT,AT
一般有这两种常见需求(测试数据见文末)
1.得到所有的不重复的值，如
value
AT
BT
CT
DT
ET
SQL如下：
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
SQL如下：
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)
实现思路：实现思路：
需求1：
1. 通过group_concat函数将value列的值拼接成一个逗号隔开的字符串，然后通过substring_index函数对字符串进行截取
2. 通过substring_index函数特性，我们就需要知道字符串有多少个逗号，并且要告诉每个逗号的位置
3. 逗号个数=char_length(字符串)-char_length(replace(字符串,’,’,”))
4. 逗号位置=mysql.help_topic.id < 逗号个数[+1] 5. 最后通过distinct函数将截取后的单个值进行去重
注意：注意：
1. mysql.help_topic表的自增id是从0开始，所以在进行截取时要对id进行+1。见: substring_index(a.col,’,’,b.help_topic_id+1)
评论1