MySQL 使用序列化表的方法实现行转列

最新推荐文章于 2021-12-26 01:44:47 发布

GALA1212

最新推荐文章于 2021-12-26 01:44:47 发布

阅读量883

点赞数

分类专栏： MySQL 文章标签： mysql 行专列

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

创建 t_user表

CREATE TABLE `t_user` (
  `id` int(30) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(30) DEFAULT NULL,
  `mobile` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

insert  into `t_user`(`id`,`user_name`,`mobile`) values (1,'张三','18294829382,18492819382,18829392918');
insert  into `t_user`(`id`,`user_name`,`mobile`) values (2,'李四','13828374827,18782939291,15929392919,14728392918');
insert  into `t_user`(`id`,`user_name`,`mobile`) values (3,'王五','15929392919');

“id” “name” “phone”
“1” “张三” “18294829382,18492819382,18829392918”
“2” “李四” “13828374827,18782939291,15929392919,14728392918”
“3” “王五” “15929392919”

创建序列表

CREATE TABLE `t_sequence` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `t_sequence` */

insert  into `t_sequence`(`id`) values (1);
insert  into `t_sequence`(`id`) values (2);
insert  into `t_sequence`(`id`) values (3);
insert  into `t_sequence`(`id`) values (4);
insert  into `t_sequence`(`id`) values (5);
insert  into `t_sequence`(`id`) values (6);
insert  into `t_sequence`(`id`) values (7);
insert  into `t_sequence`(`id`) values (8);
insert  into `t_sequence`(`id`) values (9);
insert  into `t_sequence`(`id`) values (10);

使用序列化表的方法实现行转列

SELECT user_name 
    ,REPLACE(SUBSTRING(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGTH(
SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','')AS mobile
    FROM t_sequence a CROSS JOIN (
    SELECT user_name,CONCAT(mobile,',')AS mobile,LENGTH(mobile)-
LENGTH(REPLACE(mobile,',',''))+1 size 
    FROM t_user b)b ON a.id<=b.size

“user_name” “mobile”
“张三” “18294829382”
“张三” “18492819382”
“张三” “18829392918”
“李四” “13828374827”
“李四” “18782939291”
“李四” “15929392919”
“李四” “14728392918”
“王五” “15929392919”