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)
2. value列最后一个字符不是逗号时:逗号个数+1是为了截取时不漏掉最后一个逗号后的值,即: char_length(a.col) – char_length(replace(a.col,’,’,”))+1;
value列最后一个字符是逗号时:逗号个数就不需要+1了,直接:char_length(a.col) – char_length(replace(a.col,’,’,”))
3. 因为截取时id要+1,所以在连接时取的< ,而不是 <= 。见:b.help_topic_id < (char_length(a.col) – char_length(replace(a.col,’,’,”))[+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中将一列以逗号分隔的值行转列的实现的文章就介绍到这了,更多相关mysql 逗号分割行转列内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐