mysql 行转列 列转行

一、行转列

即将原本同一列下多行的不同内容作为多个字段，输出对应内容。

建表语句

DROP TABLE IF EXISTS tb_score;

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

6、动态，适用于列不确定情况

SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;

SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
-- SELECT @QQ;

PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 

个人总结：

SET @EE=''; 
-- 将要转成行的字段单独查询出来，查询出的结果作为标题
SELECT @EE:=CONCAT(@EE,'BID_AMOUNT as ',NAME,',') FROM (SELECT DISTINCT mi.name FROM member_offer_record mor,member_info mi,bid_package bp WHERE bp.package_code='资材20190612-002' 
  AND mor.plan_id=bp.plan_id AND mor.member_id=mi.member_id) M ;

-- 再拼接 其余要查询的字段
SET @QQ=CONCAT('SELECT plan_id ,',LEFT(@EE,CHAR_LENGTH(@EE)-1) ,'   FROM member_offer_record group by plan_id  ');
-- 定义一个名字 stmt
 PREPARE stmt FROM  @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;