MySQL中列轉行和行轉列總結解決思路

2023-02-01 18:02:31

引言

在學習sql中遇到了列轉行和行轉列的題目，這裡總結一下如何在對應的情景下解決不同的題目；

列轉行

建立一個表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;

如果想要把這個錶轉為下面的形式：

+--------+---------+-------+
| 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來實現列轉行操作：

select name, 'chinese' as project, chinese as score from stu_score_01
union all
select name, 'math' as project, math as score from stu_score_01
union all
select name, 'english' as project, english as score from stu_score_01;

簡單解釋一下：分別查詢每一個科目的所有情況，求並集即可；比如單獨執行一下sql：

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

接下來只需要一次類推求出所有情況集合求並即可；

union和union all都是求的表的並集，但是union會有去重和排序操作，效率低於union all，這裡不需要去重，所以使用union all保證效率；

行轉列

建立一個表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;

如果想要單獨把每一行科目分別轉化為不同的列，如：

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

可以使用case…when和max/sum和group by來實現：

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_03
group by name;
# 或者使用sum
select 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_03
group by name;

簡單解釋一下：

因為要查詢每個人的不同科目成績，所以需要對不同的人進行分組，所以需要使用group by，不然查出來的成績誰都不知道知道是誰的；

對於每一個case when，比如：case when project = 'chinese' then score else 0 end

意思就是當project為chinese時獲取score，否則就取0；其他也是一樣的意思

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

因為先判斷的是chinese科目，如果張三首先出現的科目是math，那麼他先走chinese科目的判斷，因為math不等於chinese，

所以給chinese科目賦值為0；

所以會看到如下效果：

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_03
group by name;
#執行結果
+--------+---------+------+---------+
| name   | chinese | math | english |
+--------+---------+------+---------+
| 小六 	| 0       | 0    | 88      |
| 張三 	| 111     | 0    | 0       |
| 李四 	| 0       | 0    | 109     |
| 王五 	| 0       | 0    | 107     |
+--------+---------+------+---------+

因為小六最先出現的是english成績，所以他的chinese和math成績都被賦予值為0，

而張三最先出現的是chinese成績，所以他的math和english成績也被賦予值為0；

如果使用max或者sum，那麼max會在出現的所有值的情況下（包括0）去最大的值，其實就是實際的分數，只是把0的情況去除了；

而sum是加上了所有值，因為除了實際分數外其他都是0，所以可以直接相加；