MySQL中将一列以逗号分隔的值行转列的实现
作者:https://blog.csdn.net/github_33809414/article/details/86656216 发布时间:2024-01-20 15:31:23
前言
有时会遇到没有遵守第一范式设计模式的业务表。即一列中存储了多个属性值。如下表
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');
参考:
https://blog.csdn.net/liuzhoulong/article/details/51729168
https://blog.csdn.net/ldl22847/article/details/47609727
来源:https://blog.csdn.net/github_33809414/article/details/86656216
猜你喜欢
- 网站中很多表单都会用到上传图片,logo,照片,用户也会上传图片,这个时候网站就需要一个上传图片的功能,而且在上传后希望能预览一下看上传的对
- 问题:有一个列表,每一个条目都是这篇文章的部分内容,类似这样:<div class="list">
- 线性回归属于监督学习,因此方法和监督学习应该是一样的,先给定一个训练集,根据这个训练集学习出一个线性函数,然后测试这个函数训练的好不好(即此
- 一、分块查找算法分块查找是二分法查找和顺序查找的改进方法,分块查找要求索引表是有序的,对块内结点没有排序要求,块内结点可以是有序的也可以是无
- 一.介绍多表查询就是同时查询两个或两个以上的表,因为有的时候用户在查看数据的时候,需要显示的数据来自多张表.多表查询有以下分类:交叉连接查询
- python语句与语法1.python简单语句的基本介绍>>> while True: #简单的while循环... re
- 项目背景实验结果拍摄了一组图片,数量较大,想要按顺序组合排版,比较简单的方式是在PPT中插入图片进行排版。但是PPT批量插入图片后,顺序打乱
- 最近因需要用python写一个微服务来用MQTT给硬件传输图片,其中python用的是flask框架,大概流程如下:协议为:需要将图片数据封
- 如何提取JSON数据指定内容假设我们要获取'pic_str'里的数据JSON数据{'err_no': 0,
- 一、安装1、安装git Windows 客户端—GitHub Desktop具有git功能的Windows客户端软件有很
- 对于多层感知机而言,整个模型做的事情就是接收输入生成输出。但是并不是所有的多层神经网络都一样,所以为了实现复杂的神经网络就需要神经网络块,块
- 前言最近又在重新学习编译原理了,其实两年前也复习过,当初是为了能实现通过 MySQL 的 DDL 生
- 方法组成模式方法里的所有语句都必须处在同一个归纳层次上无用的注释让代码自我表白标注为什么这样,而不是如何这样对方法表现进行描述等于重复表现这
- 1. 数据处理中很恶心,出现 RuntimeWarning: divide by zero encountered in divide发现自
- MySQL 非空约束(NOT NULL)指字段的值不能为空。对于使用了非空约束的字段,如果用户在添加数据时没有指定值,数据库系统就会报错。可
- 最近将Pytorch程序迁移到GPU上去的一些工作和思考环境:Ubuntu 16.04.3Python版本:3.5.2Pytorch版本:0
- pandas 将字符串映射为数字在有些数据集中,有些数据变量用字符串表示,但为了方便处理,往往想转换为好处理的格式,这时候不一定要用one
- 业务需求 识别验证码图片中的数字信息,用pyt
- Content Design(内容设计)即涉及产品需求也涉及到(产品和用户)互动过程中的具体环节。大多数团队中只有PM才会涉及到相关工作,一
- 第一步:python解释器,到网上下载安装下就行。网址:https://www.python.org/downloads/windows/值