開發過程中，因為歷史原因或效能原因，需要對錶的列資料轉為行數據，或行資料轉換為列資料使用，本文將介紹mysql表資料行列轉換的方法，提供完整示範範例及sql技巧。
建立測試資料表及資料
#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 | +-------------+---------------------+
#建立測試資料表及資料
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<=(length(a.name) - length(replace(a.name,',',''))+1)order by a.category_id,b.category_id;
執行結果：
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<=(length(a.name) - length(replace(a.name,',',''))+1) -> 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表資料行列轉換方法 ，更多相關內容請關注潘合平中文網。
相關推薦：
以上是關於mysql表資料行列轉換方法的講解的詳細內容。更多資訊請關注PHP中文網其他相關文章！
