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ù)需要調(diào)整這個序列的長度。
請注意，如果你的逗號分隔值個數(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;
結論
如果Mysql版本較低，使用第二種，如果可以執(zhí)行第一種示例SQL，那么推薦使用第一種，動態(tài)的。
到此這篇關于Mysql行轉列把逗號分隔的字段拆分成多行的兩種方法的文章就介紹到這了,更多相關Mysql逗號分隔字段拆分多行內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了MySQL事務、視圖、索引、備份和恢復，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2022-09-09
在本篇文章里小編給大家分享了關于刪除mysql數(shù)據(jù)表簡單方法，需要的朋友們可以參考學習下。2020-06-06
這篇文章主要介紹了MySQL使用explain命令查看與分析索引的使用情況,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
使用SKIP-GRANT-TABLES 解決 MYSQL ROOT密碼丟失
這篇文章主要介紹了使用SKIP-GRANT-TABLES 解決 MYSQL ROOT密碼丟失的相關資料,需要的朋友可以參考下2015-09-09
Mysql清空表數(shù)據(jù)庫命令truncate和delete詳解
這篇文章主要介紹了Mysql數(shù)據(jù)庫清空表truncate和delete的相關知識，本文給大家講解的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-06-06
詳解MySQL like如何查詢包含''%''的字段(ESCAPE用法)
這篇文章主要介紹了詳解MySQL like如何查詢包含'%'的字段(ESCAPE用法)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-12-12
我們在使用like %通配符時常常會引起索引失效的問題。本文主要介紹了常見的幾種方法，具有一定的參考價值，感興趣的可以了解一下2021-06-06
這篇文章主要介紹了MySQL中LBCC和MVCC的理解及常見問題示例，文章圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，感興趣的朋友可以參考一下2022-09-09
解析mysql數(shù)據(jù)庫還原錯誤:(mysql Error Code: 1005 errno 121)
本篇文章是對mysql數(shù)據(jù)庫還原錯誤:(mysql Error Code: 1005 errno 121)的解決方法進行了詳細的分析介紹，需要的朋友參考下2013-06-06
windows10+mysql8.0.11zip安裝教程詳解
這篇文章主要介紹了windows10+mysql8.0.11zip安裝教程詳解，本文給大家介紹的非常詳細，具有參考借鑒價值,需要的朋友可以參考下2018-05-05