Mysql 逗号分割&&分组拼接_mysql 拆分逗号分隔

需求：

解决思路：1.拆分   2.关联查询中文  3.根据单号，分组拼接查询的中文

开始吧！

一、拆分逗号分隔

-- 拆分
select 
r.check_report_code,
r.sample_name '原字段值',
r.sample_name1 '拆分后字段值',
s1.name '文本值'
from(
 SELECT A1.*,
 SUBSTRING_INDEX(SUBSTRING_INDEX(A1.sample_name,',',A2.help_topic_id+1),',',-1) as 'sample_name1'
 from efs_check_report A1 
 left join mysql.help_topic A2    -- 这个表是谁无所谓,需要的是它有连续足够多的索引
 on A2.help_topic_id < (LENGTH(A1.sample_name)-LENGTH(REPLACE(A1.sample_name,',',''))+1) 
) as r
LEFT JOIN efs_sample_category s1 on s1.path = r.sample_name1
ORDER BY r.check_report_code desc;

二、分组后，拼接字段

select 
rp.check_report_code,
rp.sample_name '原字段值',
GROUP_CONCAT( DISTINCT  rp.name SEPARATOR ',' ) '字段拼接显示中文' 
 from 
(select 
r1.check_report_code,
r1.sample_name,
s1.name
from(
 SELECT A1.*,
 SUBSTRING_INDEX(SUBSTRING_INDEX(A1.sample_name,',',A2.help_topic_id+1),',',-1) as 'sample_name1'
 from efs_check_report A1 
 left join mysql.help_topic A2    
 on A2.help_topic_id < (LENGTH(A1.sample_name)-LENGTH(REPLACE(A1.sample_name,',',''))+1) 
) as r1
LEFT JOIN efs_sample_category s1 on s1.path = r1.sample_name1) as rp
GROUP BY rp.check_report_code
HAVING COUNT(check_report_code) >= 1

解决啦！撒花~