mysql 几万数据行列互换_mysql行列数据转换

一、列转行

构建数据源

-- 构建数据表

create table tb_score(

id int(11) not null auto_increment,

userid char(20) not null comment '用户ID',

subject char(20) comment '科目',

score int(4) comment '成绩',

primary key (id),

unique index (id)

)engine = innodb default charset = utf8;

-- 写入数据

insert into tb_score(userid,subject,score) values ('001','语文',90);

insert into tb_score(userid,subject,score) values ('001','数学',92);

insert into tb_score(userid,subject,score) values ('001','英语',80);

insert into tb_score(userid,subject,score) values ('002','语文',88);

insert into tb_score(userid,subject,score) values ('002','数学',90);

insert into tb_score(userid,subject,score) values ('002','英语',75);

insert into tb_score(userid,subject,score) values ('003','语文',70);

insert into tb_score(userid,subject,score) values ('003','数学',85);

insert into tb_score(userid,subject,score) values ('003','英语',90);

insert into tb_score(userid,subject,score) values ('003','政治',82);

-- 显示数据

select * from tb_score方法1

列转行：控制函数(case...when...then) + 聚合函数(max/sum)+ 分组(group by)

select

userid,

(case when subject='语文' then score else 0 end) as '语文',

(case when subject='数学' then score else 0 end) as '数学',

(case when subject='英语' then score else 0 end) as '英语',

(case when subject='政治' then score else 0 end) as '政治'

from

tb_score

-- sum和max效果一样select

userid,

sum(case when subject='语文' then score else 0 end) as '语文',

sum(case when subject='数学' then score else 0 end) as '数学',

sum(case when subject='英语' then score else 0 end) as '英语',

sum(case when subject='政治' then score else 0 end) as '政治'

from

tb_score

group by

userid方法2

列转行：控制函数(if) + 聚合函数(max/sum)+ 分组(group by)

select

userid,

sum(if(subject = '语文', score, 0)) as '语文',

sum(if(subject = '数学', score, 0)) as '数学',

sum(if(subject = '英语', score, 0)) as '英语',

sum(if(subject = '政治', score, 0)) as '政治'

from

tb_score

group by

userid

二、行转列

构建数据源

-- 建表语句：

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;

-- 插入数据：

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方法

行转列：构建列 + union连接

(

select userid,'语文' as course,cn_score as score from tb_score1

union

select userid,'数据' as course,math_score as score from tb_score1

union

select userid,'英语' as course,en_score as score from tb_score1

union

select userid,'政治' as course,po_score as score from tb_score1

) order by userid