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 rLEFT JOIN efs_sample_cateGory s1 on s1.path = r.sample_name1ORDER 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.namefrom( 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 r1LEFT JOIN efs_sample_category s1 on s1.path = r1.sample_name1) as rpGROUP BY rp.check_report_codeHAVING COUNT(check_report_code) >= 1

解决啦！撒花~

来源地址：https://blog.csdn.net/qq_42162899/article/details/127528872