溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

MySQL數(shù)據(jù)庫中怎么將行轉(zhuǎn)換為列

發(fā)布時(shí)間：2020-12-07 15:17:07 來源：億速云 閱讀：389 作者：Leah 欄目：開發(fā)技術(shù)

這篇文章將為大家詳細(xì)講解有關(guān)MySQL數(shù)據(jù)庫中怎么將行轉(zhuǎn)換為列，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

MySQL行轉(zhuǎn)列操作

    所謂的行轉(zhuǎn)列操作，就是將一個(gè)表的行信息轉(zhuǎn)化為列信息，說著可能比較籠統(tǒng)，這里先舉個(gè)例子，如下：

+----+-----------+--------+-------+
| ID | USER_NAME | COURSE | SCORE |
+----+-----------+--------+-------+
|  | 張三   | 數(shù)學(xué)  |   |
|  | 張三   | 語文  |   |
|  | 張三   | 英語  |   |
|  | 李四   | 數(shù)學(xué)  |   |
|  | 李四   | 語文  |   |
|  | 李四   | 英語  |   |
|  | 王五   | 數(shù)學(xué)  |   |
|  | 王五   | 語文  |   |
|  | 王五   | 英語  |   |
+----+-----------+--------+-------+
 rows in set (0.00 sec)

+-----------+--------+--------+--------+
| user_name | 數(shù)學(xué)  | 語文  | 英語  |
+-----------+--------+--------+--------+
| 張三   |   |   |   |
| 李四   |   |   |   |
| 王五   |   |   |   |
+-----------+--------+--------+--------+
 rows in set (0.00 sec)

    上面的例子中，表1給出了三個(gè)學(xué)生的三門成績(jī)，而表2是將表1的行記錄信息(學(xué)科、姓名)轉(zhuǎn)化為列信息，并根據(jù)不同的user_name進(jìn)行分組顯示。

1 case when操作方法

    要實(shí)現(xiàn)上面的功能，我們需要進(jìn)行分析，首先，我們需要生成三個(gè)列，分別是數(shù)學(xué)，語文和英語，然后給每個(gè)列中的值填入對(duì)應(yīng)的數(shù)據(jù)。這里需要用到mysql的case when then end操作，也就是條件操作，關(guān)于這個(gè)條件語句，首先我們給出解釋：

case colume 
  when condition1 then result1
  when condition2 then result2
  when condition3 then result3
else result4
end

   上面的語法，可以理解為當(dāng)column的值符合condition1的時(shí)候，用result1去替換column的值，以此類推，當(dāng)column值都不符合的時(shí)候，用result4去替換column的值。

    現(xiàn)在開始試驗(yàn)：

    首先我們創(chuàng)建一張表，并插入如下數(shù)據(jù)：

mysql-yeyz ::>>select * from test_tbl;
+----+-----------+--------+-------+
| ID | USER_NAME | COURSE | SCORE |
+----+-----------+--------+-------+
|  | 張三   | 數(shù)學(xué)  |   |
|  | 張三   | 語文  |   |
|  | 張三   | 英語  |   |
|  | 李四   | 數(shù)學(xué)  |   |
|  | 李四   | 語文  |   |
|  | 李四   | 英語  |   |
|  | 王五   | 數(shù)學(xué)  |   |
|  | 王五   | 語文  |   |
|  | 王五   | 英語  |   |
+----+-----------+--------+-------+
 rows in set (0.00 sec)

    根據(jù)上面case when語法，當(dāng)碰到課程為'數(shù)學(xué)'的時(shí)候，我們定義一個(gè)列'數(shù)學(xué)'，并把它的score填入其中，如果碰到'語文'或者'英語'，那么把它替換為0，我們可以先籠統(tǒng)的寫出如下SQL:

mysql-yeyz ::>>SELECT user_name ,
(CASE course WHEN '數(shù)學(xué)' THEN score ELSE END ) 數(shù)學(xué) 
FROM test_tbl;
+-----------+--------+
| user_name | 數(shù)學(xué)  |
+-----------+--------+
| 張三   |   |
| 張三   |    |
| 張三   |    |
| 李四   |   |
| 李四   |    |
| 李四   |    |
| 王五   |   |
| 王五   |    |
| 王五   |    |
+-----------+--------+
 rows in set (0.00 sec)

    我們發(fā)現(xiàn)上面的表只有兩個(gè)列，根據(jù)SQL規(guī)則，我們可以把'語文'和'英語'的值也加入進(jìn)去，一次性多寫幾個(gè)列，如下：

mysql-yeyz ::>>SELECT user_name ,
  ->   (CASE course WHEN '數(shù)學(xué)' THEN score ELSE END ) 數(shù)學(xué),
  ->   (CASE course WHEN '語文' THEN score ELSE END ) 語文,
  ->   (CASE course WHEN '英語' THEN score ELSE END ) 英語
  -> FROM test_tbl;
+-----------+--------+--------+--------+
| user_name | 數(shù)學(xué)  | 語文  | 英語  |
+-----------+--------+--------+--------+
| 張三   |   |    |    |
| 張三   |    |   |    |
| 張三   |    |    |   |
| 李四   |   |    |    |
| 李四   |    |   |    |
| 李四   |    |    |   |
| 王五   |   |    |    |
| 王五   |    |   |    |
| 王五   |    |    |   |
+-----------+--------+--------+--------+
 rows in set (0.00 sec)

    這下好了，我們有了所有的記錄了，已經(jīng)快要達(dá)到我們的目的了，看看和最終結(jié)果的差距：

+-----------+--------+--------+--------+
| user_name | 數(shù)學(xué)  | 語文  | 英語  |
+-----------+--------+--------+--------+
| 張三   |   |   |   |
| 李四   |   |   |   |
| 王五   |   |   |   |
+-----------+--------+--------+--------+

    好像就剩把那些同名的學(xué)生信息都合并一下就好了，自然而然我們想到了group_by(user_name)的操作，而group_by操作需要和一些聚合函數(shù)(MAX,MIN,AVG,SUM，COUNT等)進(jìn)行搭配。由于每條記錄中只包含當(dāng)前學(xué)科的成績(jī)，其他學(xué)科的成績(jī)?yōu)?，所以我們使用MAX函數(shù)和SUM函數(shù)的結(jié)果是相同的，但是不能使用AVG函數(shù)和MIN函數(shù)，這應(yīng)該很好理解吧。

    下面我們給出最終結(jié)果：

mysql-yeyz 13:55:52>>SELECT user_name ,
  ->   MAX(CASE course WHEN '數(shù)學(xué)' THEN score ELSE END ) 數(shù)學(xué),
  ->   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 | 數(shù)學(xué)  | 語文  | 英語  |
+-----------+--------+--------+--------+
| 張三   |   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 '數(shù)學(xué)' THEN score ELSE END ) 數(shù)學(xué),
  ->   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 | 數(shù)學(xué)  | 語文  | 英語  |
+-----------+--------+--------+--------+
| 張三   |   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 '數(shù)學(xué)' THEN score ELSE END ) 數(shù)學(xué),
  ->   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 | 數(shù)學(xué)  | 語文  | 英語  |
+-----------+--------+--------+--------+
| 張三   |   0 |   0 |   0 |
| 李四   |   0 |   0 |   0 |
| 王五   |   0 |   0 |   0 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)

     可以看出來，使用MAX和使用SUM的結(jié)果是一樣的，但是使用MIN作為聚合函數(shù)，會(huì)導(dǎo)致最終輸出的結(jié)果都是0，因?yàn)槊看味歼x的是該user_name指定學(xué)科的最小的值，也就是0。這樣的結(jié)果就很好理解了。

2 if操作方法

    上面的case  when操作方法理解了，那么if的操作方法也很好理解，原理是一樣的，只不過是把case when的語法轉(zhuǎn)換為if方式，如下

mysql-yeyz 14:12:42>>SELECT user_name ,
  ->   MAX(if (course= '數(shù)學(xué)',score,) ) 數(shù)學(xué),
  ->   MAX(if (course= '語文',score,) ) 語文,
  ->   MAX(if (course= '英語',score,) ) 英語
  -> FROM test_tbl
  -> GROUP BY USER_NAME;
+-----------+--------+--------+--------+
| user_name | 數(shù)學(xué)  | 語文  | 英語  |
+-----------+--------+--------+--------+
| 張三   |   34 |   58 |   58 |
| 李四   |   45 |   87 |   45 |
| 王五   |   76 |   34 |   89 |
+-----------+--------+--------+--------+
3 rows in set (0.00 sec)

3 添加total列

    當(dāng)我們把基本的行轉(zhuǎn)列實(shí)現(xiàn)之后，我們現(xiàn)在需要在轉(zhuǎn)換之后的表上面添加一個(gè)total字段，這個(gè)字段的添加我們可以通過下面的方法，即在最開始統(tǒng)計(jì)的時(shí)候，就把score值也統(tǒng)計(jì)進(jìn)去，如下：

mysql-yeyz 14:18:06>>SELECT user_name ,
  ->   (CASE course WHEN '數(shù)學(xué)' THEN score ELSE END ) 數(shù)學(xué),
  ->   (CASE course WHEN '語文' THEN score ELSE END ) 語文,
  ->   (CASE course WHEN '英語' THEN score ELSE END ) 英語,
  -> (score) total
  -> FROM test_tbl;
+-----------+--------+--------+--------+-------+
| user_name | 數(shù)學(xué)  | 語文  | 英語  | 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)

    上面的結(jié)果是沒有聚合后的結(jié)果，這里需要注意的是，如果我們要聚合，前三個(gè)列可以使用sum或者max的方法，最后一個(gè)列一定要使用sum的方法，因?yàn)槲覀円蟮氖强偝煽?jī)，使用max的方法會(huì)導(dǎo)致取值變?yōu)榉謹(jǐn)?shù)最高的那個(gè)值。最后的sql如下：

mysql-yeyz 14:18:29>>SELECT user_name ,
  ->   sum(CASE course WHEN '數(shù)學(xué)' THEN score ELSE END ) 數(shù)學(xué),
  ->   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 | 數(shù)學(xué)  | 語文  | 英語  | total |
+-----------+--------+--------+--------+-------+
| 張三   |   34 |   58 |   58 |  150 |
| 李四   |   45 |   87 |   45 |  177 |
| 王五   |   76 |   34 |   89 |  199 |
+-----------+--------+--------+--------+-------+
3 rows in set (0.00 sec)

4 簡(jiǎn)單方法 group_concat

    如果我們對(duì)于結(jié)果的顯示格式要求不是那么細(xì)致的話，也可以用一種粗獷的方法，就是group_concat函數(shù)，將所有的列都寫在一起，用一個(gè)字段表示，效果如下：

mysql-yeyz 14:19:13>>SELECT user_name,
GROUP_CONCAT(`course`,":",score)AS 成績(jī) 
FROM test_tbl 
GROUP BY user_name;
+-----------+-------------------------------+
| user_name | 成績(jī)             |
+-----------+-------------------------------+
| 張三   | 數(shù)學(xué):34,語文:58,英語:58    |
| 李四   | 數(shù)學(xué):45,語文:87,英語:45    |
| 王五   | 數(shù)學(xué):76,語文:34,英語:89    |
+-----------+-------------------------------+
3 rows in set (0.00 sec)

關(guān)于MySQL數(shù)據(jù)庫中怎么將行轉(zhuǎn)換為列就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手