目錄

• 引言
• 列轉(zhuǎn)行
• 行轉(zhuǎn)列
• 總結(jié)

引言

在學(xué)習(xí)sql中遇到了列轉(zhuǎn)行和行轉(zhuǎn)列的題目，這里總結(jié)一下如何在對(duì)應(yīng)的情景下解決不同的題目；

列轉(zhuǎn)行

創(chuàng)建一個(gè)表stu_score_01：

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for stu_score_01-- ----------------------------DROP TABLE IF EXISTS `stu_score_01`;CREATE TABLE `stu_score_01` (  `id` varchar(255) NOT NULL,  `name` varchar(255) NOT NULL,  `chinese` varchar(255) DEFAULT NULL,  `math` varchar(255) DEFAULT NULL,  `english` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of stu_score_01-- ----------------------------BEGIN;INSERT INTO `stu_score_01` VALUES ("1", "張三", "111", "109", "98");INSERT INTO `stu_score_01` VALUES ("2", "李四", "89", "119", "109");INSERT INTO `stu_score_01` VALUES ("3", "王五", "96", "102", "107");INSERT INTO `stu_score_01` VALUES ("4", "小六", "56", "78", "88");COMMIT;SET FOREIGN_KEY_CHECKS = 1;

如果想要把這個(gè)表轉(zhuǎn)為下面的形式：

+--------+---------+-------+
| name | project | score |
+--------+---------+-------+
| 張三 | chinese | 111 |
| 李四 | chinese | 89 |
| 王五 | chinese | 96 |
| 小六 | chinese | 56 |
| 張三 | math | 109 |
| 李四 | math | 119 |
| 王五 | math | 102 |
| 小六 | math | 78 |
| 張三 | english | 98 |
| 李四 | english | 109 |
| 王五 | english | 107 |
| 小六 | english | 88 |
+--------+---------+-------+

那么就可以使用union或者union all來(lái)實(shí)現(xiàn)列轉(zhuǎn)行操作：

select name, "chinese" as project, chinese as score from stu_score_01union allselect name, "math" as project, math as score from stu_score_01union allselect name, "english" as project, english as score from stu_score_01;

簡(jiǎn)單解釋一下：分別查詢每一個(gè)科目的所有情況，求并集即可；比如單獨(dú)執(zhí)行一下sql：

select name, "chinese" as project, chinese as score from stu_score_01;#結(jié)果+--------+---------+-------+| name   | project | score |+--------+---------+-------+| 張三 	| chinese | 111   || 李四 	| chinese | 89    || 王五 	| chinese | 96    || 小六 	| chinese | 56    |+--------+---------+-------+

接下來(lái)只需要一次類推求出所有情況集合求并即可；

union和union all都是求的表的并集，但是union會(huì)有去重和排序操作，效率低于union all，這里不需要去重，所以使用union all保證效率；

行轉(zhuǎn)列

創(chuàng)建一個(gè)表stu_score_03：

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for stu_score_03-- ----------------------------DROP TABLE IF EXISTS `stu_score_03`;CREATE TABLE `stu_score_03` (  `id` varchar(255) NOT NULL,  `name` varchar(255) NOT NULL,  `project` varchar(255) DEFAULT NULL,  `score` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of stu_score_03-- ----------------------------BEGIN;INSERT INTO `stu_score_03` VALUES ("1", "張三", "chinese", "111");INSERT INTO `stu_score_03` VALUES ("10", "李四", "english", "109");INSERT INTO `stu_score_03` VALUES ("11", "王五", "english", "107");INSERT INTO `stu_score_03` VALUES ("12", "小六", "english", "88");INSERT INTO `stu_score_03` VALUES ("2", "李四", "chinese", "89");INSERT INTO `stu_score_03` VALUES ("3", "王五", "chinese", "96");INSERT INTO `stu_score_03` VALUES ("4", "小六", "chinese", "56");INSERT INTO `stu_score_03` VALUES ("5", "張三", "math", "109");INSERT INTO `stu_score_03` VALUES ("6", "李四", "math", "119");INSERT INTO `stu_score_03` VALUES ("7", "王五", "math", "102");INSERT INTO `stu_score_03` VALUES ("8", "小六", "math", "78");INSERT INTO `stu_score_03` VALUES ("9", "張三", "english", "98");COMMIT;SET FOREIGN_KEY_CHECKS = 1;

如果想要單獨(dú)把每一行科目分別轉(zhuǎn)化為不同的列，如：

+--------+---------+------+---------+| name   | chinese | math | english |+--------+---------+------+---------+| 小六 	| 56      | 78   | 88      || 張三 	| 111     | 109  | 98      || 李四 	| 89      | 119  | 109     || 王五 	| 96      | 102  | 107     |+--------+---------+------+---------+

可以使用case…when和max/sum和group by來(lái)實(shí)現(xiàn)：

select name,max(case when project = "chinese" then score else 0 end) as "chinese",max(case when project = "math" then score else 0 end) as "math",max(case when project = "english" then score else 0 end) as "english"from stu_score_03group by name;# 或者使用sumselect name,sum(case when project = "chinese" then score else 0 end) as "chinese",sum(case when project = "math" then score else 0 end) as "math",sum(case when project = "english" then score else 0 end) as "english"from stu_score_03group by name;

簡(jiǎn)單解釋一下：

因?yàn)橐樵兠總€(gè)人的不同科目成績(jī)，所以需要對(duì)不同的人進(jìn)行分組，所以需要使用group by，不然查出來(lái)的成績(jī)誰(shuí)都不知道知道是誰(shuí)的；

對(duì)于每一個(gè)case when，比如：case when project = 'chinese' then score else 0 end

意思就是當(dāng)project為chinese時(shí)獲取score，否則就取0；其他也是一樣的意思

還有為什么需要加上max或者sum，先想象一下如果不加上max或者sum會(huì)有什么樣的效果：

因?yàn)橄扰袛嗟氖莄hinese科目，如果張三首先出現(xiàn)的科目是math，那么他先走chinese科目的判斷，因?yàn)閙ath不等于chinese，

所以給chinese科目賦值為0；

所以會(huì)看到如下效果：

select name,case when project = "chinese" then score else 0 end as "chinese",case when project = "math" then score else 0 end as "math",case when project = "english" then score else 0 end as "english"from stu_score_03group by name;#執(zhí)行結(jié)果+--------+---------+------+---------+| name   | chinese | math | english |+--------+---------+------+---------+| 小六 	| 0       | 0    | 88      || 張三 	| 111     | 0    | 0       || 李四 	| 0       | 0    | 109     || 王五 	| 0       | 0    | 107     |+--------+---------+------+---------+

因?yàn)樾×钕瘸霈F(xiàn)的是english成績(jī)，所以他的chinese和math成績(jī)都被賦予值為0，

而張三最先出現(xiàn)的是chinese成績(jī)，所以他的math和english成績(jī)也被賦予值為0；

如果使用max或者sum，那么max會(huì)在出現(xiàn)的所有值的情況下（包括0）去最大的值，其實(shí)就是實(shí)際的分?jǐn)?shù)，只是把0的情況去除了；

而sum是加上了所有值，因?yàn)槌藢?shí)際分?jǐn)?shù)外其他都是0，所以可以直接相加；

總結(jié)

說(shuō)了這么多，其實(shí)可以總結(jié)兩句話：

列轉(zhuǎn)行，查詢需要的每列數(shù)據(jù)使用union或者union all求并集

行轉(zhuǎn)列，使用case…when分情況查詢數(shù)據(jù)，group by和sum/max進(jìn)行篩選

到此這篇關(guān)于MySQL中列轉(zhuǎn)行和行轉(zhuǎn)列總結(jié)解決思路的文章就介紹到這了,更多相關(guān)MySQL列轉(zhuǎn)行和行轉(zhuǎn)列內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！