当前位置：首页 > article >正文

Mysql 行转列，把逗号分隔的字段拆分成多行

article 2025/2/24 16:35:46

效果如下

源数据

在这里插入图片描述

变更后的数据

在这里插入图片描述

方法

第一种

先执行下面的SQL，看不看能不能执行，如果有结果，代表数据库版本是可以的，可以看下面和自己表关联的SQL，如果不行用第二种。

示例SQL

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num 
FROM mysql.help_topic 
WHERE help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

和业务结合在一起使用

SELECT
		a.store_signer_name,
		substring_index( substring_index( a.concatStoreId, ',', b.help_topic_id + 1 ), ',', - 1 ) AS concatStoreId 
	FROM
		(select store_signer_nameconcatStoreId from test) a
		INNER JOIN mysql.help_topic b ON b.help_topic_id < (
			length( a.concatStoreId ) - length(
			REPLACE ( a.concatStoreId, ',', '' )) + 1)

其核心在于mysql.help_topic，但是版本太低的数据库版本不支持，如果不支持，可以用下面第二种。

第二种

示例SQL

SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(table_name.csv_values, ',', numbers.n), ',', -1) AS split_value
FROM
  table_name
  INNER JOIN
  (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
   SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
   SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
   SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL
   SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40) numbers
  ON CHAR_LENGTH(table_name.csv_values) - CHAR_LENGTH(REPLACE(table_name.csv_values, ',', '')) >= numbers.n - 1;

在上面的查询中，因为我逗号分隔的最大个数是36，所以我添加了40个UNION ALL SELECT子句，以生成数字序列1到40。你可以根据需要调整这个序列的长度。

请注意，如果你的逗号分隔值个数大于40，那么你需要相应地增加数字序列的长度。

和业务结合在一起使用

SELECT
table_name.store_signer_name,
table_name.store_signer_contact,
  SUBSTRING_INDEX(SUBSTRING_INDEX(table_name.concatStoreId, ',', numbers.n), ',', -1) AS store_id
FROM
  (select store_signer_name,store_signer_contact,GROUP_CONCAT(store_id) concatStoreId from t_store_esgin_info where business_status = 1003 and type =0 and start_year = 2023  group by store_signer_name,store_signer_contact having count(1) > 1) table_name
  INNER JOIN
  (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
   SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
   SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
   SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL
   SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40) numbers
  ON CHAR_LENGTH(table_name.concatStoreId) - CHAR_LENGTH(REPLACE(table_name.concatStoreId, ',', '')) >= numbers.n - 1;