mysql 分割逗号分隔的字段_mysql 一行数据的不同字段 逗号分隔 差集

已知有表A:

 表B:

我们想要得到:

 

 建表

CREATE TABLE if not EXISTS A 
(
用户编号 VARCHAR(10),
用户地址 VARCHAR(20),
金额 INT
);
 
INSERT INTO A VALUES
('001','花开村1号',10),
('002','花开村2号',15),
('003','花开村6号',15),
('004','花开村5号',16),
('005','花开村12号',8);
 
CREATE TABLE B 
(
微信用户订单号 VARCHAR(10),
用户编号 VARCHAR(50)
);
 
INSERT INTO B VALUES
('90002','001,002,004'),
('90003','005');
 
 
SELECT * from A;
SELECT * from B;

 实现:

with tab1 as(
 SELECT A1.用户编号 as old
			 ,A1.微信用户订单号
			 ,SUBSTRING_INDEX(SUBSTRING_INDEX(A1.用户编号,',',A2.help_topic_id+1),',',-1) as '用户编号'
 from B A1 
 left join mysql.help_topic A2     -- 这个表是谁无所谓,需要的是它有连续足够多的索引
 on A2.help_topic_id < (LENGTH(A1.用户编号)-LENGTH(REPLACE(A1.用户编号,',',''))+1) 
)
SELECT A1.*,A2.`微信用户订单号`
from A A1
LEFT JOIN tab1 A2
on A1.`用户编号` = A2.`用户编号`

这个join最基本原理是笛卡尔积。通过这个方式来实现循环。

分析：

`length(A1.path) - length(replace(A1.path,',',''))+1` 表示了，按照逗号分割后，分割需要循环的次数。

join过程：

根据ID进行循环

{

　　判断：i 是否 <= n

　　获取最靠近第 i 个逗号之前的数据， 即 substring_index(substring_index(a.path,',',b.help_topic_id),',',-1)

}

这种方法的缺点在于，我们需要一个拥有连续数列的独立表。并且连续数列的最大值一定要大于符合分割的值的个数。

例如有一行的path有100个逗号分割的值，那么我们的table 就需要有至少100个连续行。

如使用mysql内部表mysql.help_topic： help_topic_id 共有699个数值，一般能满足于大部分需求了。