欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySql行转列、列转行

程序员文章站 2022-03-09 22:17:57
现mysql中有一张表php_user表,表结构为: 表中数据有: 现在想查询出来不同学生的语数外成绩在一行显示,那么需要用到行转列的用法, 一、行转列 1、使用case...when....then 进行行转列MAX(case when 条件 then 列内容 else 不匹配时显示内容 end) ......

现mysql中有一张表php_user表,表结构为: 

MySql行转列、列转行

表中数据有: 

MySql行转列、列转行

现在想查询出来不同学生的语数外成绩在一行显示,那么需要用到行转列的用法,

一、行转列

1、使用case...when....then 进行行转列
max(case when 条件 then 列内容 else 不匹配时显示内容 end) 列名。具体sql如下:

MySql行转列、列转行

2、使用if() 进行行转列:

MySql行转列、列转行

3、利用sum(if()) 生成列,直接生成结果不再利用子查询

MySql行转列、列转行

二、列转行

 建表语句:

MySql行转列、列转行
create table tb_score1(
    id int(11) not null auto_increment,
    userid varchar(20) not null comment '用户id',
    cn_score double comment '语文成绩',
    math_score double comment '数学成绩',
    en_score double comment '英语成绩',
    po_score double comment '政治成绩',
    primary key(id)
)engine = innodb default charset = utf8;
MySql行转列、列转行

 插入数据:

insert into tb_score1(userid,cn_score,math_score,en_score,po_score) values ('001',90,92,80,0);
insert into tb_score1(userid,cn_score,math_score,en_score,po_score) values ('002',88,90,75.5,0);
insert into tb_score1(userid,cn_score,math_score,en_score,po_score) values ('003',70,85,90,82);

 查询数据表中的内容(即转换前的结果)

select * from tb_score1

MySql行转列、列转行

转换后:

MySql行转列、列转行

本质是将userid的每个科目分数分散成一条记录显示出来。

直接上sql:

MySql行转列、列转行
select userid,'语文' as course,cn_score as score from tb_score1
union all
select userid,'数学' as course,math_score as score from tb_score1
union all
select userid,'英语' as course,en_score as score from tb_score1
union all
select userid,'政治' as course,po_score as score from tb_score1
order by userid
MySql行转列、列转行

这里将每个userid对应的多个科目的成绩查出来,通过union all将结果集加起来,达到上图的效果。

附:union与union all的区别(摘):

   1.对重复结果的处理:union会去掉重复记录,union all不会;

   2.对排序的处理:union会排序,union all只是简单地将两个结果集合并;

   3.效率方面的区别:因为union 会做去重和排序处理,因此效率比union all慢很多;