MySQL實現行列轉換

2022-07-08 14:08:28

實際應用中，會遇到需要把表的某些行轉換成列，或者把列轉換成行的情況。比如一張表在資料庫中是這樣的：

圖1

但是，需要的結果可能是這樣：

圖2

這個時候就得行列轉換了。

1.行轉列的幾種方法

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

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

另一種寫法：

case course
when '化學' then score
else 0
end

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

1.2 if (`欄位名1`=‘欄位值’，，)

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

貌似比第一種方法簡潔一些，所以下面的擴充套件是基於這種方法的~

以上兩種轉換方法結果相同，如圖2。另外實際應用中還可能需要有總計的結果，如圖3.

圖3

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

圖4

圖5

1.3 if (`欄位名1`=‘欄位值’，，) + union

把兩個查詢結果拼接到一起就是圖3的樣子了，程式碼如下：

select uid,uname,
sum(if(`course`='英語',score,0)) '英語',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化學',score,0)) '化學',
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`='化學',score,0)) '化學',
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`='化學',score,0)) '化學',
sum(score) 'total'
from course
group by uid
with ROLLUP

比1.3簡潔一些，效率應該也高一點。with rollup和group by配套使用，會在已有的查詢結果上再多出一行，對結果再聚合成一行，即圖5的那一行，若不是數位型別，則返回最下面一行的資料，最後一行分組的欄位會顯示null，因此在配合ifnull（）就可以了。

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

2.列轉行

列轉行剛好和行轉列情況相反，即：

資料庫中儲存的是這樣

圖2

而我們需要這樣的結果

圖1

沒有在建立新表，暫且把這個檢視當成新表吧

create view rtc as
select ifnull(uid,'Total') uid,uname,
sum(if(`course`='英語',score,0)) '英語',
sum(if(`course`='物理',score,0)) '物理',
sum(if(`course`='化學',score,0)) '化學',
sum(score) 'total'
from course
group by uid
with ROLLUP
-- 下面是列轉行程式碼
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,'化學' ,化學 from rtc where uid <>'Total' and 化學>0

到此這篇關於MySQL實現行列轉換的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。