Mysql行轉列把逗號分隔的字段拆分成多行兩種方法

更新時間：2024年05月29日 10:54:57 作者：技術武器庫

在數(shù)據(jù)庫中有時候我們需要將一些行數(shù)據(jù)轉換為列數(shù)據(jù),這在處理數(shù)據(jù)透視表、報表生成等場景下非常常見,這篇文章主要給大家介紹了關于Mysql行轉列把逗號分隔的字段拆分成多行的兩種方法,需要的朋友可以參考下

效果如下

源數(shù)據(jù)

變更后的數(shù)據(jù)

方法

第一種

先執(zhí)行下面的SQL，看不看能不能執(zhí)行，如果有結果，代表數(shù)據(jù)庫版本是可以的，可以看下面和自己表關聯(lián)的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

和業(yè)務結合在一起使用

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，但是版本太低的數(shù)據(jù)庫版本不支持，如果不支持，可以用下面第二種。

第二種

示例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;

在上面的查詢中，因為我逗號分隔的最大個數(shù)是36，所以我添加了40個UNION ALL SELECT子句，以生成數(shù)字序列1到40。你可以根據(jù)需要調整這個序列的長度。

請注意，如果你的逗號分隔值個數(shù)大于40，那么你需要相應地增加數(shù)字序列的長度。

和業(yè)務結合在一起使用

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;