MySQL實現(xiàn)行列轉(zhuǎn)換

更新時間：2022年07月08日 10:29:09 作者：小旭2021

這篇文章介紹了MySQL實現(xiàn)行列轉(zhuǎn)換的方法，文中通過示例代碼介紹的非常詳細。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下

實際應(yīng)用中，會遇到需要把表的某些行轉(zhuǎn)換成列，或者把列轉(zhuǎn)換成行的情況。比如一張表在數(shù)據(jù)庫中是這樣的：

圖1

但是，需要的結(jié)果可能是這樣：

圖2

這個時候就得行列轉(zhuǎn)換了。

1.行轉(zhuǎn)列的幾種方法

1.1 case... when ... then ... else ... end

select uname,uid, -- 正常查詢的字段
sum(
case
when course ='英語' then score -- 需要轉(zhuǎn)換的字段
else 0
end) '英語',
sum(
case
when course= '物理' then score
else 0
end) '物理',
sum(
case 
when course='化學(xué)' then score
else 0
end) '化學(xué)'
from course
group by uid

另一種寫法：

case course
when '化學(xué)' then score
else 0
end

另外若省略‘else 0‘，則沒有該課程的同學(xué)的分數(shù)會填充為null; sum替換成max結(jié)果一樣。

1.2 if (`字段名1`=‘字段值’，，)

select uname,uid,
sum(if(`course`='英語',score,0)) '英語',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化學(xué)',score,0)) '化學(xué)'
from course
group by uname

貌似比第一種方法簡潔一些，所以下面的擴展是基于這種方法的~

以上兩種轉(zhuǎn)換方法結(jié)果相同，如圖2。另外實際應(yīng)用中還可能需要有總計的結(jié)果，如圖3.

圖3

total這一列簡單，直接在之前的查詢基礎(chǔ)上加一個sum(score) 'total'即可；Total這一行則可以看成之前的查詢不加group by而聚合成一行。因此可以看做是兩個表組合到一起如圖4和圖5：

圖4

圖5

1.3 if (`字段名1`=‘字段值’，，) + union

把兩個查詢結(jié)果拼接到一起就是圖3的樣子了，代碼如下：

select uid,uname,
sum(if(`course`='英語',score,0)) '英語',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化學(xué)',score,0)) '化學(xué)',
sum(score) 'total'
from course
group by uname
union
select 'Total',null,
sum(if(`course`='英語',score,0)) '英語',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化學(xué)',score,0)) '化學(xué)',
sum(score) 'total'
from course

1.4 if (`字段名1`=‘字段值’，，) + IFNULL（）+with rollup

這種方法效果同1.3，

select ifnull(uid,'Total') uid, uname,
sum(if(`course`='英語',score,0)) '英語',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化學(xué)',score,0)) '化學(xué)',
sum(score) 'total'
from course
group by uid
with ROLLUP

比1.3簡潔一些，效率應(yīng)該也高一點。with rollup和group by配套使用，會在已有的查詢結(jié)果上再多出一行，對結(jié)果再聚合成一行，即圖5的那一行，若不是數(shù)字類型，則返回最下面一行的數(shù)據(jù)，最后一行分組的字段會顯示null，因此在配合ifnull（）就可以了。

有瑕疵，想把它變成 null，有待完善。

2.列轉(zhuǎn)行

列轉(zhuǎn)行剛好和行轉(zhuǎn)列情況相反，即：

數(shù)據(jù)庫中存儲的是這樣

圖2

而我們需要這樣的結(jié)果

圖1

沒有在創(chuàng)建新表，暫且把這個視圖當(dāng)成新表吧

create view rtc as
select ifnull(uid,'Total') uid,uname,
sum(if(`course`='英語',score,0)) '英語',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化學(xué)',score,0)) '化學(xué)',
sum(score) 'total'
from course
group by uid
with ROLLUP
-- 下面是列轉(zhuǎn)行代碼
select uid,uname,'英語' course,英語 score from rtc where uid <>'Total' and 英語>0
union all select uid,uname,'物理' ,物理 from rtc where uid <>'Total' and 物理>0
union all select uid,uname,'化學(xué)' ,化學(xué) from rtc where uid <>'Total' and 化學(xué)>0

到此這篇關(guān)于MySQL實現(xiàn)行列轉(zhuǎn)換的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: