Mysql行转列把逗号分隔的字段拆分成多行两种方法!

admin • • SQL技巧

Mysql行转列把逗号分隔的字段拆分成多行两种方法!

在数据库中有时候我们需要将一些行数据转换为列数据,这在处理数据透视表、报表生成等场景下非常常见,这篇文章主要给大家介绍了关于Mysql行转列把逗号分隔的字段拆分成多行的两种方法,需要的朋友可以参考下

效果如下

源数据

2024052910501419

变更后的数据

2024052910501420

方法

第一种

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

示例SQL

1
2
3
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

和业务结合在一起使用

1
2
3
4
5
6
7
8
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

1
2
3
4
5
6
7
8
9
10
11
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，那么你需要相应地增加数字序列的长度。

和业务结合在一起使用

1
2
3
4
5
6
7
8
9
10
11
12
13
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;

结论

如果Mysql版本较低，使用第二种，如果可以执行第一种示例SQL，那么推荐使用第一种，动态的。

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

大型电视艺术教学片《汉字宫》！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需99元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>