這篇文章給大家分享的是有關(guān)MySQL 中行列轉(zhuǎn)換的SQL技巧有哪些的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過來看看吧。

創(chuàng)新互聯(lián)公司是一家以網(wǎng)站建設(shè)公司、網(wǎng)頁設(shè)計(jì)、品牌設(shè)計(jì)、軟件運(yùn)維、營銷推廣、小程序App開發(fā)等移動(dòng)開發(fā)為一體互聯(lián)網(wǎng)公司。已累計(jì)為OPP膠袋等眾行業(yè)中小客戶提供優(yōu)質(zhì)的互聯(lián)網(wǎng)建站和軟件開發(fā)服務(wù)。

行列轉(zhuǎn)換常見場景

由于很多業(yè)務(wù)表因?yàn)闅v史原因或者性能原因，都使用了違反第一范式的設(shè)計(jì)模式。即同一個(gè)列中存儲(chǔ)了多個(gè)屬性值（具體結(jié)構(gòu)見下表）。 這種模式下，應(yīng)用常常需要將這個(gè)列依據(jù)分隔符進(jìn)行分割，并得到列轉(zhuǎn)行的結(jié)果。

表數(shù)據(jù)：

期望得到結(jié)果：

具體方法

先從一個(gè)具體實(shí)例開始我們的介紹：

#準(zhǔn)備示例數(shù)據(jù)

create table tbl_name (ID int ,mSize varchar(100));

insert into tbl_name values (1,'tiny,small,big');

insert into tbl_name values (2,'small,medium');

insert into tbl_name values (3,'tiny,big');

#用于行列轉(zhuǎn)換循環(huán)的自增表

create table incre_table (AutoIncreID int);

insert into incre_table values (1);

insert into incre_table values (2);

insert into incre_table values (3);

#實(shí)現(xiàn)行列轉(zhuǎn)換的SQL

select a.ID,substring_index(substring_index(a.mSize,',',b.AutoIncreID),',',-1)

from

tbl_name a

join

incre_table b

on b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,',',''))+1)

order by a.ID;

原理分析： 這個(gè)join最基本原理是笛卡爾積。通過這個(gè)方式來實(shí)現(xiàn)循環(huán)。 以下是具體問題分析：length(a.Size) - length(replace(a.mSize,',',''))+1 表示了，按照逗號(hào)分割后，改列擁有的數(shù)值數(shù)量，下面簡稱n join過程的偽代碼：

根據(jù)ID進(jìn)行循環(huán)

{

    判斷：i 是否 <= n

    {

        獲取最靠近第 i 個(gè)逗號(hào)之前的數(shù)據(jù)， 即 substring_index(substring_index(a.mSize,',',b.ID),',',-1)

        i = i +1

    }

    ID = ID +1

}

改進(jìn)版本

上面一種方法方法的缺點(diǎn)在于，我們需要一個(gè)擁有連續(xù)數(shù)列的獨(dú)立表（也就是上文中的incre_table)。并且連續(xù)數(shù)列的最大值一定要大于符合分割的值的個(gè)數(shù)。 例如有一行的mSize 有100個(gè)逗號(hào)分割的值，那么我們的incre_table 就需要有至少100個(gè)連續(xù)行。 當(dāng)然，mysql內(nèi)部也有現(xiàn)成的連續(xù)數(shù)列表可用。如mysql.help_topic， help_topic_id 共有504個(gè)數(shù)值，一般能滿足于大部分需求了。

改寫后如下:

select a.ID,substring_index(substring_index(a.mSize,',',b.help_topic_id+1),',',-1)

from

tbl_name a

join

mysql.help_topic b

on b.help_topic_id < (length(a.mSize) - length(replace(a.mSize,',',''))+1)

order by a.ID;

感謝各位的閱讀！關(guān)于“MySQL 中行列轉(zhuǎn)換的SQL技巧有哪些”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！