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_ic_id+1),',',-1)) from (select group_concat(distinct `value`) as col from `row_to_col`) as a join mysql.help_ic as b on b.help_ic_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_ic_id+1),',',-1) from (select `value` as col,pk from `row_to_col`) as a join mysql.help_ic as b on b.help_ic_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_ic.id < 逗号个数[+1]
5. 通过distinct函数将截取后的单个值进行去重
注意
1. mysql.help_ic表的自增id是从0开始,所以在进行截取时要对id进行+1。见: substring_index(a.col,',',b.help_ic_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_ic_id < (char_length(a.col) - char_length(replace(a.col,',',''))[+1])
4. mysql.help_ic(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 逗号分割行转列内容请搜索狼蚁SEO以前的文章或继续浏览狼蚁网站SEO优化的相关文章希望大家以后多多支持狼蚁SEO!
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南