MySQL 行轉(zhuǎn)列詳情

更新時(shí)間：2022年01月11日 14:16:13 作者：_陳哈哈

這篇文章主要介紹了MySQL 行轉(zhuǎn)列詳情，MySQL 行轉(zhuǎn)列語句不難，具體的詳細(xì)資料，感興趣的小伙伴可以參考一下

MySQL行轉(zhuǎn)列，對經(jīng)常處理數(shù)據(jù)的同學(xué)們來說，一定是不陌生的，甚至是印象深刻，因?yàn)樗蟾怕世_過你，讓你為之一愣~ 但當(dāng)你看到本文后，這個(gè)問題就不在是問題，及時(shí)收藏，以后誰再問你這個(gè)問題，直接甩他臉上，粘貼即用。

首先，我們看一下咱們的測試表數(shù)據(jù)和預(yù)期查詢的結(jié)果：

mysql> SELECT * FROM t_gaokao_score;
+----+--------------+--------------+-------+
| id | student_name | subject ? ? ?| score |
+----+--------------+--------------+-------+
| ?1 | 林磊兒 ? ? ? | 語文 ? ? ? ? | ? 148 |
| ?2 | 林磊兒 ? ? ? | 數(shù)學(xué) ? ? ? ? | ? 150 |
| ?3 | 林磊兒 ? ? ? | 英語 ? ? ? ? | ? 147 |
| ?4 | 喬英子 ? ? ? | 語文 ? ? ? ? | ? 121 |
| ?5 | 喬英子 ? ? ? | 數(shù)學(xué) ? ? ? ? | ? 106 |
| ?6 | 喬英子 ? ? ? | 英語 ? ? ? ? | ? 146 |
| ?7 | 方一凡 ? ? ? | 語文 ? ? ? ? | ? ?70 |
| ?8 | 方一凡 ? ? ? | 數(shù)學(xué) ? ? ? ? | ? ?90 |
| ?9 | 方一凡 ? ? ? | 英語 ? ? ? ? | ? ?59 |
| 10 | 方一凡 ? ? ? | 特長加分 ? ? | ? 200 |
| 11 | 陳哈哈 ? ? ? | 語文 ? ? ? ? | ? 109 |
| 12 | 陳哈哈 ? ? ? | 數(shù)學(xué) ? ? ? ? | ? ?92 |
| 13 | 陳哈哈 ? ? ? | 英語 ? ? ? ? | ? ?80 |
+----+--------------+--------------+-------+
13 rows in set (0.00 sec)

看看我們行轉(zhuǎn)列轉(zhuǎn)完后的結(jié)果:

+--------------+--------+--------+--------+--------------+
| student_name | 語文 ? | 數(shù)學(xué) ? | 英語 ? | 特長加分 ? ? |
+--------------+--------+--------+--------+--------------+
| 林磊兒 ? ? ? | ? ?148 | ? ?150 | ? ?147 | ? ? ? ? ? ?0 |
| 喬英子 ? ? ? | ? ?121 | ? ?106 | ? ?146 | ? ? ? ? ? ?0 |
| 方一凡 ? ? ? | ? ? 70 | ? ? 90 | ? ? 59 | ? ? ? ? ?200 |
| 陳哈哈 ? ? ? | ? ?109 | ? ? 92 | ? ? 80 | ? ? ? ? ? ?0 |
+--------------+--------+--------+--------+--------------+
4 rows in set (0.00 sec)

好，下面我們一起來看看SQL是如何編寫的，對了，創(chuàng)建表結(jié)構(gòu)和導(dǎo)入測試數(shù)據(jù)的SQL放到文章末尾了，自取~

一、行轉(zhuǎn)列SQL寫法

方法一、使用case..when..then進(jìn)行行轉(zhuǎn)列

ELECT student_name,
?? ?SUM(CASE `subject` WHEN '語文' THEN score ELSE 0 END) as '語文',
?? ?SUM(CASE `subject` WHEN '數(shù)學(xué)' THEN score ELSE 0 END) as '數(shù)學(xué)',
?? ?SUM(CASE `subject` WHEN '英語' THEN score ELSE 0 END) as '英語',
?? ?SUM(CASE `subject` WHEN '特長加分' THEN score ELSE 0 END) as '特長加分'?
FROM t_gaokao_score?
GROUP BY student_name;

這里如果不使用SUM()會(huì)報(bào)sql_mode=only_full_group_by相關(guān)錯(cuò)誤，需要聚合函數(shù)和group by連用或使用distinct才可以解決。

其實(shí)，加了SUM()是為了能夠使用GROUP BY根據(jù)student_name進(jìn)行分組，每一個(gè)student_name對應(yīng)的subject="語文"的記錄畢竟只有一條，所以SUM() 的值就等于對應(yīng)那一條記錄的score的值。當(dāng)然，也可以換成MAX()。

方法二、使用IF()進(jìn)行行轉(zhuǎn)列：

ELECT student_name,
?? ?SUM(IF(`subject`='語文',score,0)) as '語文',
?? ?SUM(IF(`subject`='數(shù)學(xué)',score,0)) as '數(shù)學(xué)',
?? ?SUM(IF(`subject`='英語',score,0)) as '英語',
?? ?SUM(IF(`subject`='特長加分',score,0)) as '特長加分'?
FROM t_gaokao_score?
GROUP BY student_name;

該方法將IF(subject='語文',score,0)作為條件，通過student_name進(jìn)行分組，對分組后所有subject='語文’的記錄的score字段進(jìn)行SUM()操作，如果score沒有值則默認(rèn)為0。

這種方式和case..when..then方法原理相同，相比更加簡潔明了，建議使用。

二、如果領(lǐng)導(dǎo)@你，讓你在結(jié)果集中加上總數(shù)列呢？

友情提示：我們工作中處理行轉(zhuǎn)列數(shù)據(jù)時(shí)，盡量都把總數(shù)、平均數(shù)等加上，方便領(lǐng)導(dǎo)查閱，省得他循環(huán)BB你。

話說，你還記得上學(xué)時(shí)的成績表是啥樣的么？你一般從上往下看還是從下往上看呢？文末投票，快來給大家樂呵樂呵！

寫法：利用SUM(IF()) 生成列，WITH ROLLUP 生成匯總列和行,并利用 IFNULL將匯總行標(biāo)題顯示為總數(shù)

SELECT IFNULL(student_name,'總數(shù)') AS student_name,
?? ?SUM(IF(`subject`='語文',score,0)) AS '語文',
?? ?SUM(IF(`subject`='數(shù)學(xué)',score,0)) AS '數(shù)學(xué)',
?? ?SUM(IF(`subject`='英語',score,0)) AS '英語',
?? ?SUM(IF(`subject`='特長加分',score,0)) AS '特長加分',
?? ?SUM(score) AS '總數(shù)'?
FROM t_gaokao_score
GROUP BY student_name WITH ROLLUP;

查詢結(jié)果：

+--------------+--------+--------+--------+--------------+--------+
| student_name | 語文 ? | 數(shù)學(xué) ? | 英語 ? | 特長加分 ? ? | 總數(shù) ? |
+--------------+--------+--------+--------+--------------+--------+
| 喬英子 ? ? ? | ? ?121 | ? ?106 | ? ?146 | ? ? ? ? ? ?0 | ? ?373 |
| 方一凡 ? ? ? | ? ? 70 | ? ? 90 | ? ? 59 | ? ? ? ? ?200 | ? ?419 |
| 林磊兒 ? ? ? | ? ?148 | ? ?150 | ? ?147 | ? ? ? ? ? ?0 | ? ?445 |
| 陳哈哈 ? ? ? | ? ?113 | ? ?116 | ? ? 80 | ? ? ? ? ? ?0 | ? ?309 |
| 總數(shù) ? ? ? ? | ? ?452 | ? ?462 | ? ?432 | ? ? ? ? ?200 | ? 1546 |
+--------------+--------+--------+--------+--------------+--------+
5 rows in set, 1 warning (0.00 sec)

三、領(lǐng)導(dǎo)又雙叒叕@你改需求

讓你把分值轉(zhuǎn)化為具體內(nèi)容顯示（優(yōu)秀、良好、普通、差），430分以上重點(diǎn)大學(xué)，400分以上一本，350分及以上二本，350以下搬磚，該怎么寫呢？

這里我們就需要case when嵌套一下了，看著高大上，其實(shí)就是普通的嵌套而已。在第一層查出分組后的各科分?jǐn)?shù)，在第二層替換成等級即可。

SELECT student_name,
MAX( ?
? ? ? ? CASE subject ?
? ? ? ? WHEN '語文' THEN ?
? ? ? ? ? ? ( ?
? ? ? ? ? ? ? ? CASE ?
? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='語文') > 20 THEN ?
? ? ? ? ? ? ? ? ? ? '優(yōu)秀' ?
? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='語文') > 10 THEN ?
? ? ? ? ? ? ? ? ? ? '良好' ?
? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='語文') >= 0 THEN ?
? ? ? ? ? ? ? ? ? ? '普通' ?
? ? ? ? ? ? ? ? ELSE ?
? ? ? ? ? ? ? ? ? ? '差' ?
? ? ? ? ? ? ? ? END ?
? ? ? ? ? ? ) ?
? ? ? ? END ?
? ? ) as '語文',?
MAX( ?
? ? ? ? CASE subject ?
? ? ? ? WHEN '數(shù)學(xué)' THEN ?
? ? ? ? ? ? ( ?
? ? ? ? ? ? ? ? CASE ?
? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') > 20 THEN ?
? ? ? ? ? ? ? ? ? ? '優(yōu)秀' ?
? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') > 10 THEN ?
? ? ? ? ? ? ? ? ? ? '良好' ?
? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='數(shù)學(xué)') >= 0 THEN ?
? ? ? ? ? ? ? ? ? ? '普通' ?
? ? ? ? ? ? ? ? ELSE ?
? ? ? ? ? ? ? ? ? ? '差' ?
? ? ? ? ? ? ? ? END ?
? ? ? ? ? ? ) ?
? ? ? ? END ?
? ? ) as '數(shù)學(xué)',
MAX( ?
? ? ? ? CASE subject ?
? ? ? ? WHEN '英語' THEN ?
? ? ? ? ? ? ( ?
? ? ? ? ? ? ? ? CASE ?
? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='英語') > 20 THEN ?
? ? ? ? ? ? ? ? ? ? '優(yōu)秀' ?
? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='英語') > 10 THEN ?
? ? ? ? ? ? ? ? ? ? '良好' ?
? ? ? ? ? ? ? ? WHEN score - (select avg(score) from t_gaokao_score where subject='英語') >= 0 THEN ?
? ? ? ? ? ? ? ? ? ? '普通' ?
? ? ? ? ? ? ? ? ELSE ?
? ? ? ? ? ? ? ? ? ? '差' ?
? ? ? ? ? ? ? ? END ?
? ? ? ? ? ? ) ?
? ? ? ? END ?
? ? ) as '英語',
SUM(score) as '總分',
(CASE WHEN SUM(score) > 430 THEN '重點(diǎn)大學(xué)' ?
?? ? ?WHEN SUM(score) > 400 THEN '一本' ?
?? ? ?WHEN SUM(score) > 350 THEN '二本' ?
?? ? ?ELSE '工地搬磚'?
?? ? ?END ) as '結(jié)果'
FROM t_gaokao_score?
GROUP BY student_name?
ORDER BY SUM(score) desc;

我們來看一下輸出結(jié)果：

+--------------+--------+--------+--------+--------+--------------+
| student_name | 語文 ? | 數(shù)學(xué) ? | 英語 ? | 總分 ? | 結(jié)果 ? ? ? ? |
+--------------+--------+--------+--------+--------+--------------+
| 林磊兒 ? ? ? | 優(yōu)秀 ? | 優(yōu)秀 ? | 優(yōu)秀 ? | ? ?445 | 重點(diǎn)大學(xué) ? ? |
| 方一凡 ? ? ? | 差 ? ? | 差 ? ? | 差 ? ? | ? ?419 | 一本 ? ? ? ? |
| 喬英子 ? ? ? | 普通 ? | 差 ? ? | 優(yōu)秀 ? | ? ?373 | 二本 ? ? ? ? |
| 陳哈哈 ? ? ? | 普通 ? | 普通 ? | 差 ? ? | ? ?309 | 工地搬磚 ? ? |
+--------------+--------+--------+--------+--------+--------------+
4 rows in set (0.00 sec)

過來人的經(jīng)驗(yàn)來看，老實(shí)孩子最吃虧，早知道他娘的走藝體了~

四、結(jié)束語

好了，SQL方面就是以上這些內(nèi)容了，有疑問可以寫在評論區(qū)，哈哥會(huì)在摸魚的時(shí)候回復(fù)你~~`

附錄：創(chuàng)建表結(jié)構(gòu)&測試數(shù)據(jù)SQL

表結(jié)構(gòu)：

DROP TABLE IF EXISTS `t_gaokao_score`;
CREATE TABLE `t_gaokao_score` ?(
? `id` int(0) NOT NULL AUTO_INCREMENT,
? `student_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '學(xué)生姓名',
? `subject` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目',
? `score` double NULL DEFAULT NULL COMMENT '成績',
? PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

導(dǎo)入測試數(shù)據(jù)：

INSERT INTO `t_gaokao_score` VALUES?
(1, '林磊兒', '語文', 148),
(2, '林磊兒', '數(shù)學(xué)', 150),
(3, '林磊兒', '英語', 147),
(4, '喬英子', '語文', 121),
(5, '喬英子', '數(shù)學(xué)', 106),
(6, '喬英子', '英語', 146),
(7, '方一凡', '語文', 70),
(8, '方一凡', '數(shù)學(xué)', 90),
(9, '方一凡', '英語', 59),
(10, '方一凡', '特長加分', 200),
(11, '陳哈哈', '語文', 109),
(12, '陳哈哈', '數(shù)學(xué)', 92),
(13, '陳哈哈', '英語', 80);

到此這篇關(guān)于MySQL 行轉(zhuǎn)列詳情的文章就介紹到這了,更多相關(guān)MySQL 行轉(zhuǎn)列內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: