mysql 多行转一行多列_mysql一行转多行加上一行转多列-CSDN博客

本文链接：https://blog.csdn.net/weixin_32533659/article/details/113913040

博客讲述了如何使用SQL解决从含有特定格式字符串的表中提取信息，并通过连接其他表进行复杂查询的问题。首先，将字符串中的格式替换为可解析的格式，然后利用SUBSTRING_INDEX函数拆分字符串，最后通过JOIN操作与另一个表匹配，获取最终所需的数据。

摘要由CSDN通过智能技术生成

看到群里有人有个sql不会写，平时mysql复杂写的少，就简单写了写。虽然写的很烂，但是思路还可以。。

create table t1(

xid varchar(10) ,

Name varchar(20),

Code varchar(100)

);

create table t2(

yid varchar(10) ,

Name varchar(20),

je varchar(10)

);

insert into t1(xid,Name,Code) values('1','a','[a1][1]+[b2][2]');

insert into t1(xid,Name,Code) values('2','b','[b2][3]');

insert into t1(xid,Name,Code) values('3','c','[c3][3]+[a1][1]');

insert into t2(yid,name,je) values ('a1','瓜子','1.5');

insert into t2(yid,name,je) values ('b2','花生','1');

insert into t2(yid,name,je) values ('c3','板栗','2');

他需要这样的结果：

![image-20210127142552833]()

思路如下：

select t.xid,t.name,REPLACE(REPLACE(REPLACE(REPLACE(t.code,"]+[",","),"][",":"),"[",""),"]","") as rn from t1 t

![image-20210127142720899]()

select t.xid,t.name,substring_index(

substring_index(

t.rn,

',',

b.help_topic_id + 1

',' ,- 1

) AS rn from (select t.xid,t.name,REPLACE(REPLACE(REPLACE(REPLACE(t.code,"]+[",","),"][",":"),"[",""),"]","") as rn from t1 t )t

JOIN mysql.help_topic b ON b.help_topic_id <

(length(t.rn) - length( replace(t.rn, ',', '') ) + 1)

![image-20210127142811326]()

select tt.xid,tt.name,SUBSTR(tt.rn,1,2) as yid,SUBSTR(tt.rn,4,LENGTH(tt.rn)) as num from

(select t.xid,t.name,substring_index(

substring_index(

t.rn,

',',

b.help_topic_id + 1

',' ,- 1

) AS rn from (select t.xid,t.name,REPLACE(REPLACE(REPLACE(REPLACE(t.code,"]+[",","),"][",":"),"[",""),"]","") as rn from t1 t )t

JOIN mysql.help_topic b ON b.help_topic_id <

(length(t.rn) - length( replace(t.rn, ',', '') ) + 1)) tt

![]()

select t3.xid,t3.name,t2.`Name`,t2.je,t3.num from

(select tt.xid,tt.name,SUBSTR(tt.rn,1,2) as yid,SUBSTR(tt.rn,4,LENGTH(tt.rn)) as num from

(select t.xid,t.name,substring_index(

substring_index(

t.rn,

',',

b.help_topic_id + 1

',' ,- 1

) AS rn from (select t.xid,t.name,REPLACE(REPLACE(REPLACE(REPLACE(t.code,"]+[",","),"][",":"),"[",""),"]","") as rn from t1 t )t

JOIN mysql.help_topic b ON b.help_topic_id <

(length(t.rn) - length( replace(t.rn, ',', '') ) + 1)) tt)t3

INNER JOIN t2

on t3.yid = t2.yid

![image-20210127142903098]()