行轉列與列轉行的概念

這里需要重申一下行轉列和列轉行的區別。有很多的貼子在介紹的時候沒有嚴格的區分，命名介紹的是列轉行該如何操作，但是帖子的標題確寫的是行轉列。自始至終都沒有提過列轉行的事情。所以，我覺的這里應該有必要做一次區別和認識。

什么是行轉列

所謂的行轉列是指把數據表中具有相同key值的多行value數據，轉換為使用一個key值的多列數據，使每一行數據中，一個key對應多個value。

行轉列完成后，在視覺上的效果就是：表中的總行數減少了，但是列數增加了。

如下所示的轉換過程就是一個簡單的行轉列的過程：

什么是列轉行

所謂的列轉行是指把表中同一個key值對應的多個value列，轉換為多行數據，使每一行數據中，保證一個key只對應一個value。

列轉行完成之后，在視覺上的效果就是：表中的列數減少了，但是行數增加了。

如下所示的轉換過程就是一個簡單的列轉行過程：

行轉列實驗示例

在進行實驗之前，我們需要先準備好我們的實驗環境，準備好表和表中的初始化數據。

• 準備初始化表結構

CREATE TABLE `student_x` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `class` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• 準備初始化表中的數據

INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (1, '張三', '數學', 78);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (2, '張三', '英語', 93);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (3, '張三', '語文', 65);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (4, '李四', '數學', 87);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (5, '李四', '英語', 90);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (6, '李四', '語文', 76);
INSERT INTO `student_x`(`id`, `name`, `class`, `score`) VALUES (7, '李四', '歷史', 69);

• 進行行轉列的實驗環境最后如下所示：
  

示例：使用聚合函數

我們在這個示例中，使用聚合函數sum、max、min、avg來完成我們的行轉列的需求，具體的實驗如下。

• 下面是我們需要完成行轉列的效果圖：
  
• 使用case when語句來拼裝新的數據列

select name,
	case when class = '數學' then score else null end as math_score,
	case when class = '英語' then score else null end as engilsh_score,
	case when class = '語文' then score else null end as chinese_score,
	case when class = '歷史' then score else null end as history_score
from student_x;

• 基于上面的效果圖，我們需要把數據結果，按照name列進行聚合，讓姓名相同的數據行合并為同一行來展示，同時，每一列的科目中，只有一行數據是有成績的，其他行成績都是空null，所以他們結合使用max函數，可以達到合并行，并且每列的科目成績不會因為合并行而影響到最后的每一科目的成績。實現上述轉換的SQL語句如下：

select name,
	max(case when class = '數學' then score else null end) as math_score,
	max(case when class = '英語' then score else null end) as engilsh_score,
	max(case when class = '語文' then score else null end) as chinese_score,
	max(case when class = '歷史' then score else null end) as history_score
from student_x
group by name;

• 我們上面使用了case when語句來判斷，其實if語句也可以達到case when語句的效果。如下是使用if語句的結果：

select name,
	max(if(class = '數學', score, null)) as math_score,
	max(if(class = '英語', score, null)) as engilsh_score,
	max(if(class = '語文', score, null)) as chinese_score,
	max(if(class = '歷史', score, null)) as history_score
from student_x
group by name;

• 實現過程分析

1. 這里我們使用了聚合函數max，把每一個學生的姓名作為key，進行分組統計。
2. 因為每一個學生對應每一門科目的成績只有一行記錄，所以我們使用聚合函數sum統計后的每一科目的成績，仍然是該科目單獨的成績。
3. 如果這里每一個學生對應每一門科目有多個成績記錄，這里就不能使用聚合函數max了，如果使用max，最后的結果將是每一個學生對應每一門科目成績的最大值。
4. 這里之所以使用max的目的是為了達到一個分組的效果。這里的max可以使用sum、min、avg等聚合函數替換掉，它們三個的效果和sum函數的效果在這里是一樣的。

總結：上面的這樣的實現方式，使大家經常使用的，也是大家最熟悉的一種方式。但是這樣的寫法有一個問題，就是當我們的科目名稱變動或者增加或者減少的時候，我們SQL語句也需要作出對應的修改。因為我們在SQL語句中已經使用了hard code硬編碼的方式把科目的名稱給寫死了，所以這樣的SQL不太靈活。

我們可以參考使用下面的幾種寫法，每一種寫法稍微有點不同，但是這些方式基本都能滿足我們的需求。

列轉行實驗示例

在進行列轉行的實驗之前，我們需要先準備好我們的實驗環境，準備好表和表中的初始化數據。表結構和初始化數據如下：

• 準備初始化表結構

CREATE TABLE `student_y` (
  `id` int(11) DEFAULT NULL, 
  `name` varchar(255) DEFAULT NULL,
  `math_score` bigint(255) DEFAULT NULL,
  `engilsh_score` bigint(255) DEFAULT NULL,
  `chinese_score` bigint(255) DEFAULT NULL,
  `history_score` bigint(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• 準備初始化表中的數據

INSERT INTO `student_y`(`id`, `name`, `math_score`, `engilsh_score`, `chinese_score`, `history_score`) VALUES (1, '張三', 78, 93, 65, NULL);
INSERT INTO `student_y`(`id`, `name`, `math_score`, `engilsh_score`, `chinese_score`, `history_score`) VALUES (2, '李四', 87, 90, 76, 69);

• 進行列轉行的實驗環境最后如下所示：
  

示例：使用union all功能

實驗環境準備后之后，接下來我們開始使用union all的功能來實現列轉行的功能。

• 下面是我們需要完成行轉列的效果圖：
• 我們可以對所有學生的每一個科目的成績進行單獨查詢，結果如下：

select name, math_score as score from student_y;
select name, engilsh_score as score from student_y;
select name, chinese_score as score from student_y;
select name, history_score as score from student_y;

• 基于上面的每一個查詢結果，把最后的結果使用union all關鍵詞合并在一起，效果如下：

select name, math_score as score from student_y
union all
select name, engilsh_score as score from student_y
union all
select name, chinese_score as score from student_y
union all
select name, history_score as score from student_y;

• 此時，我們發現結果中已經大概實現了列轉為行的需求。但是順序沒有達到要求，每個人的各個科目的成績應該挨著，但是目前是沒有挨著的。所以我們需要在基于上面的查詢結果，外面在包裹一層查詢，增加一個order by語句在外層查詢中，即可得到我們想要的順序。如下所示：

select * from (
	select name, math_score as score from student_y
	union all
	select name, engilsh_score as score from student_y
	union all
	select name, chinese_score as score from student_y
	union all
	select name, history_score as score from student_y
) as x order by name;

• 此時的結果已經很接近我們的最后想要的結果了，但是我們發現，每個學生的成績我們不能區分各個科目的成績是多少，所以我們需要把科目也納入到結果集中，也就是我們在查詢的時候，要把各個成績對應的列名稱也包含到查詢的字段中。于是就有了如下的SQL

select * from (
	select name, 'math_score' as class, math_score as score from student_y
	union all
	select name, 'engilsh_score' as class, engilsh_score as score from student_y
	union all
	select name, 'chinese_score' as class, chinese_score as score from student_y
	union all
	select name, 'history_score' as class, history_score as score from student_y
) as x order by name,class;

總結：這里我們采用了union all的功能，把所有學生每個科目的成績單獨查詢出來，然后把結果集繼續合并。最后達到我們想要的列轉為行的效果。但是我們發現此時的SQL語句中，和前面我們在進行行轉列的時候，使用聚合函數的方式來實現行轉列的方式類似，使用了hard code的硬編碼，如果科目名稱或數目發生改變，我們的SQL語句也需要跟著動態的去修改，這是這種方式的缺陷。但是優點就是比較容易理解。

最后總結

我們在文章的開始，首選針對行和列相互轉行的概念做了簡單澄清，因為有很多人會把行轉列和列轉行給混淆。行轉列和列轉行的最明顯的區分如下：

行轉列，最后的結果中行變少了，列變多了。
列轉行，最后的結果中列變少了，行變多了。
緊接著，我們分別針對行轉列和列轉行進行了實驗演示。