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

转换

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

字符行转列

建表

CREATE TABLE `tb_basics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL COMMENT '用户名',
  `skillType` varchar(20) DEFAULT NULL COMMENT '技能类型',
  `skillName` varchar(20) DEFAULT NULL COMMENT '技能',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

添加数据

INSERT INTO `tb_basics`(`id`, `name`, `skillType`, `skillName`) VALUES (1, '张三', '平A', '大逼兜');
INSERT INTO `tb_basics`(`id`, `name`, `skillType`, `skillName`) VALUES (2, '张三', '小绝', '退退退！');
INSERT INTO `tb_basics`(`id`, `name`, `skillType`, `skillName`) VALUES (3, '张三', '大招', '闪电五连鞭');
INSERT INTO `tb_basics`(`id`, `name`, `skillType`, `skillName`) VALUES (4, '李四', '平A', '扫堂腿');
INSERT INTO `tb_basics`(`id`, `name`, `skillType`, `skillName`) VALUES (5, '李四', '小绝', '洪家铁线拳');
INSERT INTO `tb_basics`(`id`, `name`, `skillType`, `skillName`) VALUES (6, '李四', '大招', '齐眉棍');
INSERT INTO `tb_basics`(`id`, `name`, `skillType`, `skillName`) VALUES (7, '王五', '平A', '缩骨功');
INSERT INTO `tb_basics`(`id`, `name`, `skillType`, `skillName`) VALUES (8, '王五', '小绝', '八卦掌');
INSERT INTO `tb_basics`(`id`, `name`, `skillType`, `skillName`) VALUES (9, '王五', '大招', '玄冥神掌');

转换

select  
name,
GROUP_CONCAT(case when skillType='平A' then skillName end Separator ',') as `平A`,
GROUP_CONCAT(case when skillType='小绝' then skillName end Separator ',') as `小绝`,
GROUP_CONCAT(case when skillType='大招' then skillName end Separator ',') as `大招`
from tb_basics 
group by name