MySQL将一个字段中以逗号分隔的取出来形成新的字段实现_Mysql

MySQL将一个字段中以逗号分隔的取出来形成新的字段实现

2021-02-26 17:38旧夏潜入梦 Mysql

这篇文章主要介绍了MySQL将一个字段中以逗号分隔的取出来形成新的字段实现，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧

1例如：要把如图1的字段拆分图2

MySQL将一个字段中以逗号分隔的取出来形成新的字段实现

									select account_id,

									    substring_index(substring_index(a.related_shop_ids,','

									    ,b.help_topic_id+1),',',-1) shopid

									  from

									    sales_hang_account a

									  join

									    mysql.help_topic b

									    on b.help_topic_id < (length(a.related_shop_ids) -   length(replace(a.related_shop_ids,',',''))+1) 

									  order by a.account_id

2：然后和shops表进行连接查询取出我们需要的字段，我这里需要取出name

									select s.`name` as shopname,a.account_id from shops s

									inner join (

									select account_id,

									    substring_index(substring_index(a.related_shop_ids,','

									    ,b.help_topic_id+1),',',-1) shopid

									  from

									    sales_hang_account a

									  join

									    mysql.help_topic b

									    on b.help_topic_id < (length(a.related_shop_ids) -   length(replace(a.related_shop_ids,',',''))+1) 

									  order by a.account_id)a on s.shop_id=a.shopid

MySQL将一个字段中以逗号分隔的取出来形成新的字段实现

3:将 account_id相同合并成一行，以逗号隔开

									//这两个是网上的例子

									select id,group_concat(name) as name from table group by id;

									select id,group_concat(name separator ';') as name from a group by id;

									//借助上面两个参考

									select account_id,group_concat(shopname separator',')as shopname from (select s.`name` as shopname,a.account_id from shops s

									inner join (

									select account_id,

									    substring_index(substring_index(a.related_shop_ids,','

									    ,b.help_topic_id+1),',',-1) shopid

									  from

									    sales_hang_account a

									  join

									    mysql.help_topic b

									    on b.help_topic_id < (length(a.related_shop_ids) -   length(replace(a.related_shop_ids,',',''))+1) 

									  order by a.account_id)a on s.shop_id=a.shopid) a group by account_id

效果如下

MySQL将一个字段中以逗号分隔的取出来形成新的字段实现

第二种方法

1 2	`select` `g.account_id,g.related_shop_ids,group_concat(s.name)as` `shopname` `from` `sales_hang_account g` `left` `join` `shops s` `on` `find_in_set(s.shop_id , g.related_shop_ids)` `group` `by` `g.account_id`

MySQL将一个字段中以逗号分隔的取出来形成新的字段实现

sqlserver

									[product] -- 该字段存储格式为 7,8,9,11,10,12 ，数据类型为nvarchar

									select id,productname=stuff((select ',' + product_chinaname from base_supplier_product where charindex(','+ltrim(productid)+',',','+ product + ',') > 0 for xml path('') ), 1, 1, '')

									 from base_pre_sale_project