MySQL行转列实现和总结

全栈程序员站长

发布于 2022-07-01 10:51:34

1.5K0

大家好，又见面了，我是你们的朋友全栈君。

一、行转列实例

1、准备数据

CREATE TABLE tb(`cname` VARCHAR(10),cource VARCHAR(10),score INT) ENGINE=INNODB; INSERT INTO tb VALUES('张三','语文',74); INSERT INTO tb VALUES('张三','数学',83); INSERT INTO tb VALUES('张三','物理',93); INSERT INTO tb VALUES('李四','语文',74); INSERT INTO tb VALUES('李四','数学',84); INSERT INTO tb VALUES('李四','物理',94);

最终想要的结果是这样：

| 姓名 | 语文 | 数学 | 物理 | +------+--------+--------+----+ | 张三 | 74.00 | 83.00 | 93.00 | | 李四 | 74.00 | 84.00 | 94.00 |

2、利用SUM(IF()) 生成列

SELECT cname AS "姓名", SUM(IF(cource="语文", score, 0)) AS "语文", SUM(IF(cource="数学", score, 0)) AS "数学", SUM(IF(cource="物理", score, 0)) AS "物理" FROM tb GROUP BY cname

张三  74  83  93
李四  74  84  94

3，利用max(CASE … WHEN … THEN .. ELSE END) AS “语文”的方式来实现

 SELECT cname AS "姓名", MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理" FROM tb GROUP BY `cname`

张三  74  83  93
李四  74  84  94

4、分析

原有的数据是这样的：

cname cource score
张三  语文  74
张三  数学  83
张三  物理  93
李四  语文  74
李四  数学  84
李四  物理  94

想变成这样：

姓名    语文  数学  物理
张三  74  83  93
李四  74  84  94

暂且将原先的表称为A，之后的称为B，A想成为B，主要是讲A表中cource列中的行数据变为列，抠除行转列的语法之外，使用了GROUP BY cname进行分组，A表的业务主键是cname和cource，这是一个联合主键。

SELECT * FROM tb GROUP BY cname

张三  语文  74
李四  语文  74

总结一：行转列，分组(GROUP BY)的列必须是除需要行转列之外的业务主键。例如tb表中业务主键应该是cname和cource,但是cource需要进行行转列，所以需要按照cname分组。

这里的字段比较少，体现的不明显，可以在tb表的基础上再加一列，性别：

cname cource score gender
张三  语文  74  男
张三  数学  83  男
张三  物理  93  男
李四  语文  74  男
李四  数学  84  男
李四  物理  94  男
张三  语文  80  女
张三  数学  80  女
张三  物理  80  女

这时候业务主键是cname,cource,gender，要向进行行转列，SQL应该是这样的：

SELECT cname AS "姓名", gender AS "性别", MAX(CASE cource WHEN "语文" THEN score ELSE 0 END) AS "语文", MAX(CASE cource WHEN "数学" THEN score ELSE 0 END) AS "数学", MAX(CASE cource WHEN "物理" THEN score ELSE 0 END) AS "物理" FROM tb GROUP BY `cname`, gender

显示结果是：

张三  女   80  80  80
张三  男   74  83  93
李四  男   74  84  94

如果还是以cname分组，结果会是这样：

张三  男   80  83  93
李四  男   74  84  94

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/131581.html原文链接：https://javaforall.cn

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

https

java

网络安全

MySQL行转列实现和总结

MySQL行转列实现和总结

一、行转列实例

1、准备数据

2、利用SUM(IF()) 生成列

3，利用max(CASE … WHEN … THEN .. ELSE END) AS “语文”的方式来实现

4、分析

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐