對(duì)于mysql表數(shù)據(jù)行列轉(zhuǎn)換方法的講解

開發(fā)過程中，因?yàn)闅v史原因或性能原因，需要對(duì)表的列數(shù)據(jù)轉(zhuǎn)為行數(shù)據(jù)，或行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)使用，本文將介紹mysql表數(shù)據(jù)行列轉(zhuǎn)換的方法，提供完整演示例子及sql技巧。

1.行轉(zhuǎn)列

創(chuàng)建測(cè)試數(shù)據(jù)表及數(shù)據(jù)

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      
+-------------+--------+

行轉(zhuǎn)列后，期望得到以下結(jié)果

+-------------+---------------------+  category_id   name                 
+-------------+---------------------+            1   大,中,小             
            2   奔馳,寶馬            
            3   2015,2016,2017,2018              4   1m,2m                
+-------------+---------------------+

行轉(zhuǎn)列，可以使用group_concat()函數(shù)結(jié)合group by實(shí)現(xiàn)。

group_concat()函數(shù)可以得到表達(dá)式結(jié)合體的連結(jié)值，默認(rèn)分隔符為逗號(hào)，可以通過separator設(shè)置為其他分隔符。

注意：group_concat()函數(shù)對(duì)返回的結(jié)果有長(zhǎng)度限制，默認(rèn)為1024字節(jié)，不過對(duì)于正常的情況已經(jīng)足夠。

關(guān)于group_concat()函數(shù)的使用可以參考我之前的文章：《mysql函數(shù)concat與group_concat使用說明》

執(zhí)行結(jié)果：

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.列轉(zhuǎn)行

創(chuàng)建測(cè)試數(shù)據(jù)表及數(shù)據(jù)

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                
+-------------+---------------------+

列轉(zhuǎn)行后，期望得到以下結(jié)果

+-------------+--------+  category_id   name    
+-------------+--------+            1   大      
            1   中      
            1   小      
            2   奔馳    
            2   寶馬    
            3   2015    
            3   2016    
            3   2017    
            3   2018    
            4   1m                  4   2m      
+-------------+--------+

列轉(zhuǎn)行比行轉(zhuǎn)列復(fù)雜，對(duì)于列內(nèi)容是用分隔符分隔的數(shù)據(jù)，我們可以使用substring_index()函數(shù)進(jìn)行分割輸出，并結(jié)合笛卡爾積來實(shí)現(xiàn)循環(huán)。

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;

執(zhí)行結(jié)果：

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表數(shù)據(jù)行列轉(zhuǎn)換方法 ，更多相關(guān)內(nèi)容請(qǐng)關(guān)注潘合平中文網(wǎng)。

相關(guān)推薦：

如何通過php 實(shí)現(xiàn)多個(gè)一維數(shù)組合拼成二維數(shù)組的方法

講解php 返回?cái)?shù)組中指定多列的相關(guān)方法

關(guān)于php 基于redis計(jì)數(shù)器類的詳解

以上就是關(guān)于mysql表數(shù)據(jù)行列轉(zhuǎn)換方法的講解的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！