开发者

MySQL中列转行和行转列总结解决思路

开发者 https://www.devze.com 2023-01-31 09:04 出处:网络 作者: YXXYX
目录引言列转行行转列总结引言 在学习sql中遇到了列转行和行转列的题目,这里总结一下如何在对应的情景下解决不同的题目;
目录
  • 引言
  • 列转行
  • 行转列
  • 总结

引言

在学习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;

MySQL中列转行和行转列总结解决思路

如果想要把这个表转为下面的形式:

+--------+---------+-------+

| 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    |

+---OWwqVtj-----+---------+-------+

那么就可以使用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;

简单解释一下:分别查询每一个科目的所有情况,求并集即可;比如单独执行一下www.devze.comsql:

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');
www.devze.comINSERT 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;

MySQL中列转行和行转列总结解决思路

如果想要单独把每一行科目分别转化为不同的列,如:

+--------+---------+------+---------+
| 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 en开发者_PostgreSQLd) 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,所以可以直接相加;

总结

说了这么多,其实可以总结两句话:

列转行,查询需要的每列数据使用union或者union all求并集

行转列,使用case…when分情况查询数据,group by和sum/max进行筛选

到此这篇关于mysql中列转行和行转列总结解决思路的文章就介绍到这了,更多相关MySQL列转行和行转列内容请搜索我们以前php的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号