行轉列及列轉行查詢

開發過程中常遇到行轉列或是列轉行的問題，即需要將數據庫中一張表信息進行行轉列操作，再將每列（即每個字段）作為與其他表進行聯表查詢的字段進行顯示。

一、行轉列：將原來同一列下多行的不同內容作為多個字段，輸出對應內容

數據庫表中的內容：

轉換后：

解析：

　　行轉列是將原來的subject字段的多行內容選出來，作為結果集中的不同列，并根據code進行分組顯示對應的score

---

?mysql> select s.name 姓名,s.code 學號,

??? -> sum(if(e.subject='語文',e.score,0)) 語文成績,

??? -> sum(if(e.subject='數學',e.score,0)) 數學成績,

??? -> sum(if(e.subject='英語',e.score,0)) 英語成績

??? -> from student s

??? -> left join exam e

??? -> on s.code=e.code

??? -> group by s.code;

---

　　①sum()函數是為了能夠使用group by根據code進行分組，因為每一個student.code對應的exam.subject='語文'的記錄只有一條，所以sum()的值就等于對應那一條記錄的exam.score的值。

　　(正常情況下此記錄都是只有一條，當然如果有多條的話，可以使用sum()、max()、avg()、min()等聚合函數也都可以達到行轉列的效果)

　　②if(e.subject='語文',e.score,0)作為條件，即對所有經過group by s.code分組的e.subject='語文'的記錄的e.score字段進行SUM()、MAX（）、MIN()、AVG()操作，如果e.score沒有值則默認為0。

　　(當然使用case when then作為條件進行信息提取也是OK的)

?

二、列轉行：將sid的每個科目分數分散成一條記錄顯示

數據庫中的表：

轉換后：

解析：

??? 使用union all將每個sname對應的多個科目的成績的結果集加起來

---

?mysql> select sname,sid,'語文' as subject,語文 as score from transcript

??? -> union all

??? -> select sname,sid,'數學' as subject,數學 as score from transcript

??? -> union all

??? -> select sname,sid,'英語' as subject,英語 as score from transcript

? ? -> order by sname;?

---

?UNION與UNION ALL的區別：

　　1.對重復結果的處理：UNION會去掉重復記錄，UNION ALL不會；

　　2.對排序的處理：UNION會排序，UNION ALL只是簡單地將兩個結果集合并；

　　3.效率方面的區別：因為UNION 會做去重和排序處理，因此效率比UNION ALL慢很多