MySQL行轉列操作
所謂的行轉列操作，就是將一個表的行信息轉化為列信息，說著可能比較籠統，這里先舉個例子，如下：
+----+-----------+--------+-------+
| ID | USER_NAME | COURSE | SCORE |
+----+-----------+--------+-------+
| | 張三 | 數學 | |
| | 張三 | 語文 | |
| | 張三 | 英語 | |
| | 李四 | 數學 | |
| | 李四 | 語文 | |
| | 李四 | 英語 | |
| | 王五 | 數學 | |
| | 王五 | 語文 | |
| | 王五 | 英語 | |
+----+-----------+--------+-------+
rows in set (0.00 sec)
+-----------+--------+--------+--------+
| user_name | 數學 | 語文 | 英語 |
+-----------+--------+--------+--------+
| 張三 | | | |
| 李四 | | | |
| 王五 | | | |
+-----------+--------+--------+--------+
rows in set (0.00 sec)
上面的例子中，表1給出了三個學生的三門成績，而表2是將表1的行記錄信息(學科、姓名)轉化為列信息，并根據不同的user_name進行分組顯示。
1 case when操作方法
要實現上面的功能，我們需要進行分析，首先，我們需要生成三個列，分別是數學，語文和英語，然后給每個列中的值填入對應的數據。這里需要用到mysql的case when then end操作，也就是條件操作，關于這個條件語句，首先我們給出解釋：
case colume
when condition1 then result1
when condition2 then result2
when condition3 then result3
else result4
end
上面的語法，可以理解為當column的值符合condition1的時候，用result1去替換column的值，以此類推，當column值都不符合的時候，用result4去替換column的值。
現在開始試驗：
首先我們創建一張表，并插入如下數據：
mysql-yeyz ::>>select * from test_tbl;
+----+-----------+--------+-------+
| ID | USER_NAME | COURSE | SCORE |
+----+-----------+--------+-------+
| | 張三 | 數學 | |
| | 張三 | 語文 | |
| | 張三 | 英語 | |
| | 李四 | 數學 | |
| | 李四 | 語文 | |
| | 李四 | 英語 | |
| | 王五 | 數學 | |
| | 王五 | 語文 | |
| | 王五 | 英語 | |
+----+-----------+--------+-------+
rows in set (0.00 sec)
根據上面case when語法，當碰到課程為'數學'的時候，我們定義一個列'數學'，并把它的score填入其中，如果碰到'語文'或者'英語'，那么把它替換為0，我們可以先籠統的寫出如下SQL:
mysql-yeyz ::>>SELECT user_name ,
(CASE course WHEN '數學' THEN score ELSE END ) 數學
FROM test_tbl;
+-----------+--------+
| user_name | 數學 |
+-----------+--------+
| 張三 | |
| 張三 | |
| 張三 | |
| 李四 | |
| 李四 | |
| 李四 | |
| 王五 | |
| 王五 | |
| 王五 | |
+-----------+--------+
rows in set (0.00 sec)
我們發現上面的表只有兩個列，根據SQL規則，我們可以把'語文'和'英語'的值也加入進去，一次性多寫幾個列，如下：
mysql-yeyz ::>>SELECT user_name ,
-> (CASE course WHEN '數學' THEN score ELSE END ) 數學,
-> (CASE course WHEN '語文' THEN score ELSE END ) 語文,
-> (CASE course WHEN '英語' THEN score ELSE END ) 英語
-> FROM test_tbl;
+-----------+--------+--------+--------+
| user_name | 數學 | 語文 | 英語 |
+-----------+--------+--------+--------+
| 張三 | | | |
| 張三 | | | |
| 張三 | | | |
| 李四 | | | |
| 李四 | | | |
| 李四 | | | |
| 王五 | | | |
| 王五 | | | |
| 王五 | | | |
+-----------+--------+--------+--------+
rows in set (0.00 sec)
這下好了，我們有了所有的記錄了，已經快要達到我們的目的了，看看和最終結果的差距：
+-----------+--------+--------+--------+
| user_name | 數學 | 語文 | 英語 |
+-----------+--------+--------+--------+
| 張三 | | | |
| 李四 | | | |
| 王五 | | | |
+-----------+--------+--------+--------+
好像就剩把那些同名的學生信息都合并一下就好了，自然而然我們想到了group_by(user_name)的操作，而group_by操作需要和一些聚合函數(MAX,MIN,AVG,SUM，COUNT等)進行搭配。由于每條記錄中只包含當前學科的成績，其他學科的成績為0，所以我們使用MAX函數和SUM函數的結果是相同的，但是不能使用AVG函數和MIN函數，這應該很好理解吧。
下面我們給出最終結果：
mysql-yeyz 13:55:52>>SELECT user_name ,
-> MAX(CASE course WHEN '數學' THEN score ELSE END ) 數學,
-> MAX(CASE course WHEN '語文' THEN score ELSE END ) 語文,
-> MAX(CASE course WHEN '英語' THEN score ELSE END ) 英語
-> FROM test_tbl
-> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | 數學 | 語文 | 英語 |
+-----------+--------+--------+--------+
| 張三 | 34 | 58 | 58 |
| 李四 | 45 | 87 | 45 |
| 王五 | 76 | 34 | 89 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)
mysql-yeyz ::>>SELECT user_name ,
-> sum(CASE course WHEN '數學' THEN score ELSE END ) 數學,
-> sum(CASE course WHEN '語文' THEN score ELSE END ) 語文,
-> sum(CASE course WHEN '英語' THEN score ELSE END ) 英語
-> FROM test_tbl
-> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | 數學 | 語文 | 英語 |
+-----------+--------+--------+--------+
| 張三 | 34 | 58 | 58 |
| 李四 | 45 | 87 | 45 |
| 王五 | 76 | 34 | 89 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)
mysql-yeyz ::>>SELECT user_name ,
-> MIN(CASE course WHEN '數學' THEN score ELSE END ) 數學,
-> MIN(CASE course WHEN '語文' THEN score ELSE END ) 語文,
-> MIN(CASE course WHEN '英語' THEN score ELSE END ) 英語
-> FROM test_tbl
-> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | 數學 | 語文 | 英語 |
+-----------+--------+--------+--------+
| 張三 | 0 | 0 | 0 |
| 李四 | 0 | 0 | 0 |
| 王五 | 0 | 0 | 0 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)
可以看出來，使用MAX和使用SUM的結果是一樣的，但是使用MIN作為聚合函數，會導致最終輸出的結果都是0，因為每次都選的是該user_name指定學科的最小的值，也就是0。這樣的結果就很好理解了。
2 if操作方法
上面的case when操作方法理解了，那么if的操作方法也很好理解，原理是一樣的，只不過是把case when的語法轉換為if方式，如下
mysql-yeyz 14:12:42>>SELECT user_name ,
-> MAX(if (course= '數學',score,) ) 數學,
-> MAX(if (course= '語文',score,) ) 語文,
-> MAX(if (course= '英語',score,) ) 英語
-> FROM test_tbl
-> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | 數學 | 語文 | 英語 |
+-----------+--------+--------+--------+
| 張三 | 34 | 58 | 58 |
| 李四 | 45 | 87 | 45 |
| 王五 | 76 | 34 | 89 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)
3 添加total列
當我們把基本的行轉列實現之后，我們現在需要在轉換之后的表上面添加一個total字段，這個字段的添加我們可以通過下面的方法，即在最開始統計的時候，就把score值也統計進去，如下：
mysql-yeyz 14:18:06>>SELECT user_name ,
-> (CASE course WHEN '數學' THEN score ELSE END ) 數學,
-> (CASE course WHEN '語文' THEN score ELSE END ) 語文,
-> (CASE course WHEN '英語' THEN score ELSE END ) 英語,
-> (score) total
-> FROM test_tbl;
+-----------+--------+--------+--------+-------+
| user_name | 數學 | 語文 | 英語 | total |
+-----------+--------+--------+--------+-------+
| 張三 | 34 | 0 | 0 | 34 |
| 張三 | 0 | 58 | 0 | 58 |
| 張三 | 0 | 0 | 58 | 58 |
| 李四 | 45 | 0 | 0 | 45 |
| 李四 | 0 | 87 | 0 | 87 |
| 李四 | 0 | 0 | 45 | 45 |
| 王五 | 76 | 0 | 0 | 76 |
| 王五 | 0 | 34 | 0 | 34 |
| 王五 | 0 | 0 | 89 | 89 |
+-----------+--------+--------+--------+-------+
9 rows in set (0.00 sec)
上面的結果是沒有聚合后的結果，這里需要注意的是，如果我們要聚合，前三個列可以使用sum或者max的方法，最后一個列一定要使用sum的方法，因為我們要求的是總成績，使用max的方法會導致取值變為分數最高的那個值。最后的sql如下：
mysql-yeyz 14:18:29>>SELECT user_name ,
-> sum(CASE course WHEN '數學' THEN score ELSE END ) 數學,
-> sum(CASE course WHEN '語文' THEN score ELSE END ) 語文,
-> sum(CASE course WHEN '英語' THEN score ELSE END ) 英語,
-> sum(score) total
-> FROM test_tbl
-> GROUP BY USER_NAME;
+-----------+--------+--------+--------+-------+
| user_name | 數學 | 語文 | 英語 | total |
+-----------+--------+--------+--------+-------+
| 張三 | 34 | 58 | 58 | 150 |
| 李四 | 45 | 87 | 45 | 177 |
| 王五 | 76 | 34 | 89 | 199 |
+-----------+--------+--------+--------+-------+
3 rows in set (0.00 sec)
4 簡單方法 group_concat
如果我們對于結果的顯示格式要求不是那么細致的話，也可以用一種粗獷的方法，就是group_concat函數，將所有的列都寫在一起，用一個字段表示，效果如下：
mysql-yeyz 14:19:13>>SELECT user_name,
GROUP_CONCAT(`course`,":",score)AS 成績
FROM test_tbl
GROUP BY user_name;
+-----------+-------------------------------+
| user_name | 成績 |
+-----------+-------------------------------+
| 張三 | 數學:34,語文:58,英語:58 |
| 李四 | 數學:45,語文:87,英語:45 |
| 王五 | 數學:76,語文:34,英語:89 |
+-----------+-------------------------------+
3 rows in set (0.00 sec)
這種方法相當于直接針對原始表做了一個分組，也能夠應付一定的應用場景。
以上就是MySQL 中行轉列的方法的詳細內容，更多關于MySQL 行轉列的資料請關注腳本之家其它相關文章！
您可能感興趣的文章:- MySQL中將一列以逗號分隔的值行轉列的實現
- 當Mysql行鎖遇到復合主鍵與多列索引詳解
- Mysql 相鄰兩行記錄某列的差值方法
- mysql指定某行或者某列的排序實現方法
- mysql 列轉行,合并字段的方法(必看)
- mysql列轉行以及年月分組實例
- mysql 列轉行的技巧(分享)
- mysql 行轉列和列轉行實例詳解
- mysql 行列動態轉換的實現(列聯表,交叉表)
- 數據庫實現行列轉換(mysql示例)
