MySQL行列互转

旧城等待， 2021-12-12 21:27 214阅读 0赞

### 列转行： ###

构建用于行转列的成绩表：

# 创建学生成绩表scores1,用于行转列测试
    create table scores1(
    sname varchar(10) not null default '',
    course varchar(10) not null default '',
    score int not null default 0
    )engine myisam charset utf8mb4;
    
    # 向成绩表scores1中插入数据
    insert into scores1 values
    ('张三','语文',74),
    ('张三','数学',83),
    ('张三','物理',93),
    ('李四','语文',74),
    ('李四','数学',84),
    ('李四','物理',94);

查看成绩表scores1的表结构：

select * from scores1

![20190712205010227.png][]

使用case when函数实现行转列：

select sname,
    max(case course when '语文' then score else 0 end) as 语文,
    max(case course when '数学' then score else 0 end) as 数学,
    max(case course when '物理' then score else 0 end) as 物理
    from scores1
    group by sname

![20190712205345181.png][]

使用if函数实现行转列：

select sname,
    max(if(course='语文',score,0)) as 语文,
    max(if(course='数学',score,0)) as 数学,
    max(if(course='物理',score,0)) as 物理
    from scores1
    group by sname

![20190712205509696.png][]

### 行转列： ###

构建用于列转行的成绩表：

# 创建学生成绩表scores2,用于列转行测试
    create table scores2(
    sname varchar(10) not null default '',
    语文 int not null default 0,
    数学 int not null default 0,
    物理 int not null default 0
    )engine myisam charset utf8mb4;
    
    # 向成绩表scores2中插入数据
    insert into scores2 values
    ('张三',74,83,93),
    ('李四',74,84,94);

查看成绩表scores2的表结构：

select * from scores2

![20190712205809617.png][]

使用union实现列转行：

select sname,'语文' as course, 语文 as score from scores2
    union select sname, '数学' as course, 数学 as score from scores2
    union select sname, '物理' as course, 物理 as score from scores2
    order by sname
    
    # 上述代码中'语文','数学'和'物理'是新建立的course字段中的值
    # 而语文、数学和物理均指成绩表scores2中的字段

![20190712210021517.png][]

参考：

[https://blog.csdn.net/qq\_41080850/article/details/95357987][https_blog.csdn.net_qq_41080850_article_details_95357987]

[https://www.cnblogs.com/ken-jl/p/8570518.html][https_www.cnblogs.com_ken-jl_p_8570518.html]

[20190712205010227.png]: /images/20211210/aac80187f13c410ea0b31b73cdd3f486.png
[20190712205345181.png]: /images/20211210/4086a383d4d74b74b76783e11e98c7ea.png
[20190712205509696.png]: /images/20211210/f6056afb708c406c92da4da8abf58044.png
[20190712205809617.png]: /images/20211210/29b7b09f5aef4fd4a7b2ae0f300d622f.png
[20190712210021517.png]: /images/20211210/4188b76e84dc4a5d9560cea52e009c19.png
[https_blog.csdn.net_qq_41080850_article_details_95357987]: https://blog.csdn.net/qq_41080850/article/details/95357987
[https_www.cnblogs.com_ken-jl_p_8570518.html]: https://www.cnblogs.com/ken-jl/p/8570518.html