【无标题】

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个数值，一般能满足于大部分需求了。

    select
      evaluation_id, evaluation_code, evaluation_user, evaluation_date, re.status, re.create_by, re.create_time, re.update_by, re.update_time,
      group_concat(pl.line_id ORDER BY pl.line_code asc) as line_ids,
      group_concat(pl.line_name ORDER BY pl.line_code asc) as line_names,pl.management_unit,re.is_latest
    from risk_evaluation re
    left join pipe_line pl on FIND_IN_SET(pl.line_id, re.line_ids)

group by evaluation_id, evaluation_code, evaluation_user, evaluation_date, re.status, re.create_by, re.create_time, re.update_by, re.update_time