使用工具: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;
查看数据表:
参考: