mysql 列转行 动态查询列

参考链接 https://cloud.tencent.com/developer/ask/223926 

建表语句 

create table tb_score(

    id int(11) not null auto_increment,

    userid varchar(20) not null comment '用户id',

    subject varchar(20) comment '科目',

    score double comment '成绩',

    primary key(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.5);

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);

       执行语句

set @sql = null;

select

  group_concat(distinct

    concat(

      "max(if(`subject` = '", `subject`, "',score,null)) as ", `subject`)

  ) into @sql

from tb_score;  #可在此处添加筛选条件 例如subject不为语文的 

 

set @sql = concat('select userid, ', @sql, '

                  from    tb_score 

                  group   by userid');

prepare stmt from @sql;

execute stmt;

deallocate prepare stmt;