数据库表的行列转换（行转列，列转行）

目录

前言

行转列

创建测试表 score1

插入测试数据

需求与通用SQL写法

列转行

创建测试表 score2

插入测试数据

需求与通用SQL写法

---

前言

在工作中，多多少少都会遇到一些数据展示的需求，开发一个接口，从数据库中查询数据返回页面展示。表结构是死的，但是查询需求却是非常灵活的。很多时候不是简单的直接从表中直接 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