Mysql 逗号分割&&分组拼接

两两ACE

已于 2022-10-26 14:02:22 修改

阅读量6.9k

点赞数 1

分类专栏： MYSQL 文章标签： mysql 数据库 sql

于 2022-10-26 11:32:45 首次发布

本文链接：https://blog.csdn.net/qq_42162899/article/details/127528872

版权

MYSQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

需求：

解决思路：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