mysql列轉(zhuǎn)行方法超詳細講解
更新時間：2023年09月10日 10:06:00 作者：bankq
mysql行列轉(zhuǎn)換在項目中應(yīng)用的極其頻繁,下面這篇文章主要給大家介紹了關(guān)于mysql列轉(zhuǎn)行方法的相關(guān)資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
一、列轉(zhuǎn)行
mysql 數(shù)據(jù)庫中，我們可能遇到將數(shù)據(jù)庫中某一列的數(shù)據(jù)（多個值，按照英文逗號分隔），轉(zhuǎn)化為多行數(shù)據(jù)（即一行轉(zhuǎn)多行），然后join關(guān)聯(lián)表，再轉(zhuǎn)化為一行數(shù)據(jù)
如：有兩張表，一用戶表，一張學(xué)科表，需要查詢學(xué)科表中的用戶姓名
用戶表
| id | username | age |
| 1 | zhangsan | 20 |
| 2 | lisi | 21 |
| 3 | wamhwu | 22 |
學(xué)科表
| id | user_ids | subject |
| 1 | 1,2,3 | 數(shù)學(xué) |
| 2 | 2,3 | 語文 |
| 3 | 1,2 | 英語 |
我們首先需要把學(xué)科表中的user_ids拆分成多行
| id | user_id | subject |
| 1 | 1 | 數(shù)學(xué) |
| 1 | 2 | 數(shù)學(xué) |
| 1 | 3 | 數(shù)學(xué) |
| 2 | 2 | 語文 |
| 2 | 3 | 語文 |
| 3 | 1 | 英語 |
| 3 | 2 | 英語 |
二、普通的實現(xiàn)方式(需要依賴 mysql.help_topic 表)
SELECT
a.id,
a.subject,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.`user_ids`, ',', b.help_topic_id + 1 ), ',',-1 ) user_id
FROM
test a
JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.`user_ids`) - LENGTH( REPLACE ( a.`user_ids`, ',', '' ) ) + 1 );三、mysql.help_topic 無權(quán)限處理辦法
mysql.help_topic 的作用是對 SUBSTRING_INDEX 函數(shù)出來的數(shù)據(jù)(也就是按照分割符分割出來的)數(shù)據(jù)連接起來做笛卡爾積。
如果 mysql.help_topic 沒有權(quán)限，可以自己創(chuàng)建一張臨時表，用來與要查詢的表連接查詢。
獲取該字段最多可以分割成為幾個字符串:
SELECT MAX(LENGTH(a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '' )) + 1) FROM `test` a;
創(chuàng)建臨時表，并給臨時表添加數(shù)據(jù):
注意:
- 臨時表必須有一列從 0 或者 1 開始的自增數(shù)據(jù)
- 臨時表表名隨意，字段可以只有一個
- 臨時表示的數(shù)據(jù)量必須比
MAX(LENGTH(a.user_ids) - LENGTH(REPLACE(a.user_ids, ',', '' )) + 1)的值大
DROP TABLE IF EXISTS `tmp_help_topic`; CREATE TABLE IF NOT EXISTS `tmp_help_topic` ( `help_topic_id` bigint(20) NOT NULL AUTO_INCREMENT , PRIMARY KEY (`help_topic_id`) ); INSERT INTO `tmp_help_topic`() VALUES (); INSERT INTO `tmp_help_topic`() VALUES (); INSERT INTO `tmp_help_topic`() VALUES (); INSERT INTO `tmp_help_topic`() VALUES (); INSERT INTO `tmp_help_topic`() VALUES (); INSERT INTO `tmp_help_topic`() VALUES (); INSERT INTO `tmp_help_topic`() VALUES (); INSERT INTO `tmp_help_topic`() VALUES (); INSERT INTO `tmp_help_topic`() VALUES (); INSERT INTO `tmp_help_topic`() VALUES ();
四、查詢函數(shù)
SELECT
a.id,a.subject,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`user_ids`, ',', b.help_topic_id), ',',-1 ) user_id
FROM
test a
JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '')) + 1 );五、join用戶表，關(guān)聯(lián)用戶名
select
t2.*,
u.username
from (
SELECT
a.id,a.subject,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`user_ids`, ',', b.help_topic_id), ',',-1 ) user_id
FROM
test a
JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '')) + 1 )
) t2 join user u
on u.id = t2.user_id| id | user_id | subject | username |
| 1 | 1 | 數(shù)學(xué) | zhangsan |
| 1 | 2 | 數(shù)學(xué) | lisi |
| 1 | 3 | 數(shù)學(xué) | wangwu |
| 2 | 2 | 語文 | lisi |
| 2 | 3 | 語文 | wangwu |
| 3 | 1 | 英語 | zhangsan |
| 3 | 2 | 英語 | lisi |
六、將多行數(shù)據(jù)轉(zhuǎn)化為一行
select
t2.*,
group_concat(u.username) username
from (
SELECT
a.id,a.subject,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`user_ids`, ',', b.help_topic_id), ',',-1 ) user_id
FROM
test a
JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`user_ids`) - LENGTH(REPLACE(a.`user_ids`, ',', '')) + 1 )
) t2 join user u
on u.id = t2.user_id
group by t2.id| id | subject | user_ids | username |
| 1 | 數(shù)學(xué) | 1,2,3 | zhangsan,lisi,wangwu |
| 2 | 語文 | 2,3 | lisi,wangwu |
| 3 | 英語 | 1,2 | zhangsan,lisi |
說明：
- SUBSTRING_INDEX(SUBSTRING_INDEX(a.user_ids, ',', b.help_topic_id), ',',-1 ) 就是獲取 tmp_help_topic 表的 help_topic_id 字段的值作為 name 字段的第幾個子串
- 使用了 join 就會把字段 user_ids 分為 (LENGTH( a.user_ids) - LENGTH(REPLACE(a.user_ids, ',', '')) + 1 ) 行，并且每行的字段剛好是 user_ids字段的第 help_topic_id 個子串
GROUP_CONCAT函數(shù)用于將GROUP BY產(chǎn)生的同一個分組中的值連接起來，返回一個字符串結(jié)果
GROUP_CONCAT函數(shù)首先根據(jù)GROUP BY指定的列進行分組，將同一組的列顯示出來，并且用分隔符分隔，由函數(shù)參數(shù)(字段名)決定要返回的列
語法結(jié)構(gòu)
GROUP_CONCAT([DISTINCT] 要連接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])
說明:
(1) 使用DISTINCT可以排除重復(fù)值
(2) 如果需要對結(jié)果中的值進行排序，可以使用ORDER BY子句
(3) SEPARATOR '分隔符'是一個字符串值，默認為逗號
總結(jié)
到此這篇關(guān)于mysql列轉(zhuǎn)行方法的文章就介紹到這了,更多相關(guān)mysql列轉(zhuǎn)行內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
相關(guān)文章
因很多同學(xué)安裝mysql總是出問題,所以下面這篇文章主要給大家介紹了關(guān)于MySQL安裝失敗的原因及解決步驟,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2022-06-06
我們在Windows操作系統(tǒng)下編程會使用到MySQL數(shù)據(jù)庫。但是有時，我們會忘記數(shù)據(jù)庫的登錄密碼？當(dāng)我們忘記了登錄密碼，無法進入mysql時，該怎么辦呢？這里我們提供mysql的登錄秘密的修改2018-02-02
這篇文章主要介紹了Mysql自增主鍵id不是以此逐級遞增的處理方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-07-07
MySQL安裝時initializing database失敗的問題解決
本文主要介紹了MySQL安裝時initializing database失敗的問題解決,文中通過圖文介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
2025-02-02 
本文是對MySQL數(shù)據(jù)入庫時特殊字符的處理進行了詳細的介紹，需要的朋友可以過來參考下，希望對大家有所幫助
2013-11-11 
本文給大家介紹了MySql深頁查詢實現(xiàn)方案,結(jié)合實例代碼給大家介紹的非常詳細,對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友參考下吧
2025-11-11 
大家好，本篇文章主要講的是MySQL導(dǎo)入與導(dǎo)出備份詳解，感興趣的同學(xué)趕快來看一看吧，對你有幫助的話記得收藏一下，方便下次瀏覽
2021-12-12 
mysql5.7使用binlog 恢復(fù)數(shù)據(jù)的方法
MySQL的binlog日志是MySQL日志中非常重要的一種日志,記錄了數(shù)據(jù)庫所有的DML操作，那么怎樣通過binlog 恢復(fù)數(shù)據(jù)，本文就詳細的來介紹一下
2021-06-06 
Mysql存儲過程、觸發(fā)器、事件調(diào)度器使用入門指南
存儲過程（Stored Procedure）是一種在數(shù)據(jù)庫中存儲復(fù)雜程序的數(shù)據(jù)庫對象。為了完成特定功能的SQL語句集，經(jīng)過編譯創(chuàng)建并保存在數(shù)據(jù)庫中，本文給大家介紹Mysql存儲過程、觸發(fā)器、事件調(diào)度器使用入門指南，感興趣的朋友一起看看吧
2022-01-01