SQL 一行转多行

使用工具：Mysql

一、问题描述

原始表splitdata如下：

将原始表转换为：

创建原始数据，SQL如下：

create table if not exists splitdata(
id int(10),
name varchar(20),
subject varchar(30));

 
insert into splitdata values
(1,'Lily','语文,英语,科学'),
(2,'Lucy','语文,英语'),
(3,'Mary','体育,科学'),
(4,'Olio','数学,体育');
insert into splitdata values(
5,'Loe','["user1","user2","user3"]');

二、解题方法

使用函数：substring_index()，replace()，length()函数，借助连续id这一列实现这一功能 ，有两种实现功能：

1. 可以自己创建一张表，里面有连续id这一列。

创建连续自增id表autoid：

create table if not exists autoid(
id int(10) auto_increment key);

insert into autoid values
(1);

查看autoid表数据：

解法一：

select a.id,name,substring_index(substring_index(subject,',',b.id),',',-1) subject from(
select id,name,replace(replace(replace(subject,'[',''),']',''),'"','') subject from splitdata) a
join autoid b
on b.id <= length(a.subject) - length(replace(a.subject,',','')) + 1;

查看数据表：

 

2.使用mysql自带的表mysql.help_topic里面的help_topic_id这一连续id字段。（无需自创表）

解法二：

select a.id,a.name,substring_index(substring_index(subject,',',b.help_topic_id + 1),',',-1) subject from(
select id,name,replace(replace(replace(subject,'[',''),']',''),'"','') subject from splitdata) a
join mysql.help_topic b
on b.help_topic_id < length(a.subject) - length(replace(a.subject,',','')) + 1;

 查看数据表：

参考：

(35条消息) mysql分隔符转行_大家都叫我菜鸡刘-CSDN博客_mysql 分割转行