主頁 > 知識庫 > MySQL 中行轉(zhuǎn)列的方法

MySQL 中行轉(zhuǎn)列的方法

熱門標(biāo)簽:房產(chǎn)智能外呼系統(tǒng)品牌 常州電銷外呼系統(tǒng)一般多少錢 沃克斯電梯外呼線路圖 地圖標(biāo)注被騙三百怎么辦 云南語音外呼系統(tǒng)平臺 天智外呼系統(tǒng) 北京人工外呼系統(tǒng)價錢 400電話鄭州申請 福州呼叫中心外呼系統(tǒng)哪家好

MySQL行轉(zhuǎn)列操作

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

+----+-----------+--------+-------+
| 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給出了三個學(xué)生的三門成績,而表2是將表1的行記錄信息(學(xué)科、姓名)轉(zhuǎn)化為列信息,并根據(jù)不同的user_name進行分組顯示。

1 case when操作方法

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

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

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

    現(xià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ù)學(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)上面的表只有兩個列,根據(jù)SQL規(guī)則,我們可以把'語文'和'英語'的值也加入進去,一次性多寫幾個列,如下:

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等)進行搭配。由于每條記錄中只包含當(dāng)前學(xué)科的成績,其他學(xué)科的成績?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ù),會導(dǎo)致最終輸出的結(jié)果都是0,因為每次都選的是該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)列實現(xiàn)之后,我們現(xiàn)在需要在轉(zhuǎn)換之后的表上面添加一個total字段,這個字段的添加我們可以通過下面的方法,即在最開始統(tǒng)計的時候,就把score值也統(tǒng)計進去,如下:

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é)果,這里需要注意的是,如果我們要聚合,前三個列可以使用sum或者max的方法,最后一個列一定要使用sum的方法,因為我們要求的是總成績,使用max的方法會導(dǎo)致取值變?yōu)榉謹(jǐn)?shù)最高的那個值。最后的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 簡單方法 group_concat

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

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

    這種方法相當(dāng)于直接針對原始表做了一個分組,也能夠應(yīng)付一定的應(yīng)用場景。

以上就是MySQL 中行轉(zhuǎn)列的方法的詳細(xì)內(nèi)容,更多關(guān)于MySQL 行轉(zhuǎn)列的資料請關(guān)注腳本之家其它相關(guān)文章!

您可能感興趣的文章:
  • MySQL中將一列以逗號分隔的值行轉(zhuǎn)列的實現(xiàn)
  • 當(dāng)Mysql行鎖遇到復(fù)合主鍵與多列索引詳解
  • Mysql 相鄰兩行記錄某列的差值方法
  • mysql指定某行或者某列的排序?qū)崿F(xiàn)方法
  • mysql 列轉(zhuǎn)行,合并字段的方法(必看)
  • mysql列轉(zhuǎn)行以及年月分組實例
  • mysql 列轉(zhuǎn)行的技巧(分享)
  • mysql 行轉(zhuǎn)列和列轉(zhuǎn)行實例詳解
  • mysql 行列動態(tài)轉(zhuǎn)換的實現(xiàn)(列聯(lián)表,交叉表)
  • 數(shù)據(jù)庫實現(xiàn)行列轉(zhuǎn)換(mysql示例)

標(biāo)簽:移動 黔東 拉薩 珠海 徐州 沈陽 沈陽 鹽城

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL 中行轉(zhuǎn)列的方法》,本文關(guān)鍵詞  MySQL,中行,轉(zhuǎn)列,的,方法,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL 中行轉(zhuǎn)列的方法》相關(guān)的同類信息!
  • 本頁收集關(guān)于MySQL 中行轉(zhuǎn)列的方法的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章