MySQL中将⼀列以逗号分隔的值⾏转列的实现
前⾔
有时会遇到没有遵守第⼀范式设计模式的业务表。即⼀列中存储了多个属性值。如下表
pk value
1ET,AT
2AT,BT
3AT,DT
4DT,CT,AT
⼀般有这两种常见需求(测试数据见⽂末)
1.得到所有的不重复的值，如
value
AT
BT
CT
DT
ET
SQL如下：
select distinct(substring_index(substring_l,',',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_l) - char_length(l,',',''))+1)
2.显⽰每个值及其对应主键，如
pk value
1ET
1AT
2AT
2BT
3AT
3DT
4DT
4CT
4AT
SQL如下：
select a.pk,substring_index(substring_l,',',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_l) - char_length(l,',',''))+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_l,',',b.help_topic_id+1)
2. value列最后⼀个字符不是逗号时：逗号个数+1是为了截取时不漏掉最后⼀个逗号后的值，即: char_l) -
char_length(l,',',''))+1；
value列最后⼀个字符是逗号时：逗号个数就不需要+1了，直接：char_l) - char_length(l,',',''))
3. 因为截取时id要+1，所以在连接时取的< ，⽽不是 <= 。见：b.help_topic_id < (char_l) -
char_length(l,',',''))[+1])
4. mysql.help_topic(mysql version:
5.7.21-1)表的⾃增id，最⼤值为636。如果group_concat后的字符串中逗号个数⼤于该值，需要⾃⼰单独处理⾃增id的值
需求2：思路基本与需求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产⽣的同⼀个分组中的值连接起来，返回⼀个字符串结果。分隔符，默认逗号。
测试数据：
DROP TABLE IF EXISTS `row_to_col`;
CREATE TABLE `row_to_col` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`value` varchar(255) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
INSERT INTO `row_to_col` VALUES ('1', 'ET,AT');
INSERT INTO `row_to_col` VALUES ('2', 'AT,BT');
INSERT INTO `row_to_col` VALUES ('3', 'AT,DT');
INSERT INTO `row_to_col` VALUES ('4', 'DT,CT,AT');
mysql group by order by到此这篇关于MySQL中将⼀列以逗号分隔的值⾏转列的实现的⽂章就介绍到这了，更多相关MYSQL 逗号分割⾏转列内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！