開發過程中，因為歷史原因或性能原因，需要對表的列數據轉為行數據，或行數據轉換為列數據使用，本文將介紹mysql表數據行列轉換的方法，提供完整演示例子及sql技巧。?
1.行轉列
創建測試數據表及數據
CREATE?TABLE?`option`?(?`category_id`?int(10)?unsigned?NOT?NULL?COMMENT?'分類id',?`name`?varchar(20)?NOT?NULL?COMMENT?'名稱',?KEY?`category_id`?(`category_id`))?ENGINE=InnoDB?DEFAULT?CHARSET=utf8; INSERT?INTO?`option`?(`category_id`,?`name`)?VALUES (1,?'大'), (1,?'中'), (1,?'小'), (2,?'奔馳'), (2,?'寶馬'), (3,?'2015'), (3,?'2016'), (3,?'2017'), (3,?'2018'), (4,?'1m'), (4,?'2m');mysql>?select?*?from?`option`; +-------------+--------+|?category_id?|?name???| +-------------+--------+|???????????1?|?大?????| |???????????1?|?中?????| |???????????1?|?小?????| |???????????2?|?奔馳???| |???????????2?|?寶馬???| |???????????3?|?2015???| |???????????3?|?2016???| |???????????3?|?2017???| |???????????3?|?2018???| |???????????4?|?1m?????||???????????4?|?2m?????| +-------------+--------+
行轉列后，期望得到以下結果
+-------------+---------------------+|?category_id?|?name????????????????| +-------------+---------------------+|???????????1?|?大,中,小????????????| |???????????2?|?奔馳,寶馬???????????| |???????????3?|?2015,2016,2017,2018?||???????????4?|?1m,2m???????????????| +-------------+---------------------+
行轉列，可以使用group_concat()函數結合group by實現。
group_concat()函數可以得到表達式結合體的連結值，默認分隔符為逗號，可以通過separator設置為其他分隔符。
注意：group_concat()函數對返回的結果有長度限制，默認為1024字節，不過對于正常的情況已經足夠。
關于group_concat()函數的使用可以參考我之前的文章：《mysql函數concat與group_concat使用說明》
?
執行結果：
mysql>?select?category_id,group_concat(name)?as?name?from?`option`?group?by?category_id?order?by?category_id; +-------------+---------------------+|?category_id?|?name????????????????| +-------------+---------------------+|???????????1?|?大,中,小????????????| |???????????2?|?奔馳,寶馬???????????| |???????????3?|?2015,2016,2017,2018?||???????????4?|?1m,2m???????????????| +-------------+---------------------+
2.列轉行??
創建測試數據表及數據
CREATE?TABLE?`option2`?(?`category_id`?int(10)?unsigned?NOT?NULL?COMMENT?'分類id',?`name`?varchar(100)?NOT?NULL?COMMENT?'名稱集合')?ENGINE=InnoDB?DEFAULT?CHARSET=utf8; INSERT?INTO?`option2`?(`category_id`,?`name`)?VALUES (1,?'大,中,小?'), (2,?'奔馳,寶馬'), (3,?'2015,2016,2017,2018'), (4,?'1m,2m');mysql>?select?*?from?`option2`; +-------------+---------------------+|?category_id?|?name????????????????| +-------------+---------------------+|???????????1?|?大,中,小????????????| |???????????2?|?奔馳,寶馬???????????| |???????????3?|?2015,2016,2017,2018?||???????????4?|?1m,2m???????????????| +-------------+---------------------+
列轉行后，期望得到以下結果
+-------------+--------+|?category_id?|?name???| +-------------+--------+|???????????1?|?大?????| |???????????1?|?中?????| |???????????1?|?小?????| |???????????2?|?奔馳???| |???????????2?|?寶馬???| |???????????3?|?2015???| |???????????3?|?2016???| |???????????3?|?2017???| |???????????3?|?2018???| |???????????4?|?1m?????||???????????4?|?2m?????| +-------------+--------+
列轉行比行轉列復雜，對于列內容是用分隔符分隔的數據，我們可以使用substring_index()函數進行分割輸出，并結合笛卡爾積來實現循環。
select?a.category_id,substring_index(substring_index(a.name,',',b.category_id),',',-1)?as?name?from?`option2`?as?ajoin?`option2`?as?b?on?b.category_id<p><strong><span style="font-size:16px">執行結果：</span></strong></p><pre class="brush:sql;toolbar:false;">mysql>?select?a.category_id,substring_index(substring_index(a.name,',',b.category_id),',',-1)?as?name?from?`option2`?as?a????->?join?`option2`?as?b?on?b.category_id?order?by?a.category_id,b.category_id; +-------------+--------+|?category_id?|?name???| +-------------+--------+|???????????1?|?大?????| |???????????1?|?中?????| |???????????1?|?小?????| |???????????2?|?奔馳???| |???????????2?|?寶馬???| |???????????3?|?2015???| |???????????3?|?2016???| |???????????3?|?2017???| |???????????3?|?2018???| |???????????4?|?1m?????||???????????4?|?2m?????| +-------------+--------+
本篇講解了mysql表數據行列轉換方法? ，更多相關內容請關注潘合平中文網。
相關推薦：
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
喜歡就支持一下吧
相關推薦
