MySQL逗号分割字段的拆分与合并

最新推荐文章于 2024-07-21 03:42:00 发布

丘色果

最新推荐文章于 2024-07-21 03:42:00 发布

阅读量334

点赞数

分类专栏： IT-技术文章标签： mysql chyer 逗号分割

本文链接：https://blog.csdn.net/chyercn/article/details/120413211

版权

IT-技术专栏收录该内容

31 篇文章 0 订阅

订阅专栏

update
	test aa,
	(
	select
		c.id,
		GROUP_CONCAT(c.prov_code) prov_code,
		GROUP_CONCAT(ifnull(p.name, c.prov_code)) prov_name
	from
		(
			SELECT
				a.id,
				SUBSTRING_INDEX(SUBSTRING_INDEX(a.i_prov_codes, ',', b.help_topic_id + 1), ',',-1) prov_code
			FROM
				(
				SELECT
					b.id,
					GROUP_CONCAT(REPLACE(prov_codes, '/', ',')) AS i_prov_codes
				FROM
					test b
				group by
					id) a
			JOIN mysql.help_topic b
				ON b.help_topic_id < (LENGTH(a.i_prov_codes) - LENGTH(REPLACE(a.i_prov_codes, ',', '')) + 1)
		) c
			left join test_province p
				on p.province_code = c.prov_code
	group by
		c.id
) d
set
	aa.cover_prov_display_name = d.prov_name
where
	aa.prov_codes = d.prov_code
	and aa.id = d.id