数据库表的行列转换（行转列，列转行）
目录
前言
在工作中，多多少少都会遇到一些数据展示的需求，开发一个接口，从数据库中查询数据返回页面展示。表结构是死的，但是查询需求却是非常灵活的。很多时候不是简单的直接从表中直接 select 获取数据就能够直接返回给页面，需要将一些判断，聚合逻辑交给 SQL 来做，才能够得到查询需求中的字段和数据。其中就可能会涉及到一些表行列的转换。
以下用MySQL分别演示下行转列与列转行的业务场景，并给出通用的SQL写法。
行转列
创建测试表 score1
create table score1 (
id int auto_increment primary key,
student_id int,
subject varchar(20),
score double
);
插入测试数据
insert into score1(student_id, subject, score) values(1, 'chinese', 70); insert into score1(student_id, subject, score) values(1, 'math', 70); insert into score1(student_id, subject, score) values(1, 'english', 70); insert into score1(student_id, subject, score) values(2, 'chinese', 80); insert into score1(student_id, subject, score) values(2, 'math', 80); insert into score1(student_id, subject, score) values(2, 'english', 80); insert into score1(student_id, subject, score) values(3, 'chinese', 90); insert into score1(student_id, subject, score) values(3, 'math', 90); insert into score1(student_id, subject, score) values(3, 'english', 90);
需求与通用SQL写法
表中的数据直接查询返回的字段如下所示：
想要返回以下的字段
通用 SQL 写法的思路就是 group by 分组 + 聚合函数 sum() + case when 条件判断 + 字段别名
具体 SQL 示例如下所示：
select student_id,
sum(case subject when 'chinese' then score else 0 end) as chinese,
sum(case subject when 'math' then score else 0 end) as math,
sum(case subject when 'english' then score else 0 end) as english
from score1
group by student_id
列转行
创建测试表 score2
create table score2 (
student_id int auto_increment primary key,
chinese double,
math double,
english double
);
插入测试数据
insert into score2(chinese, math, english) values(70, 80, 90); insert into score2(chinese, math, english) values(71, 81, 91); insert into score2(chinese, math, english) values(72, 82, 92);
需求与通用SQL写法
表中的数据直接查询返回的字段如下所示：
想要返回以下的字段
通用 SQL 写法的思路就是 字段别名 + union all
具体 SQL 示例如下所示：
select student_id, 'chinese' as subject, chinese as score from score2 where chinese is not NULL
union all
select student_id, 'math' as subject, math as score from score2 where math is not NULL
union all
select student_id, 'english' as subject, english as score from score2 where english is not NULL
原文地址：https://blog.csdn.net/typeracer/article/details/140676511
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！