mysql 竖列变成横行_mysql 横变竖 竖变横

竖变横

创表

CREATE TABLE `score` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`project` varchar(50) DEFAULT NULL,

`score` varchar(3) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

添加数据

INSERT INTO `score` VALUES ('1', '小明', '语文', '90');

INSERT INTO `score` VALUES ('2', '小明', '数学', '70');

INSERT INTO `score` VALUES ('3', '小明', '英语', '60');

INSERT INTO `score` VALUES ('4', '小红', '语文', '97');

INSERT INTO `score` VALUES ('5', '小红', '数学', '60');

INSERT INTO `score` VALUES ('6', '小红', '英语', '80');

INSERT INTO `score` VALUES ('7', '小亮', '语文', '70');

INSERT INTO `score` VALUES ('8', '小亮', '数学', '80');

INSERT INTO `score` VALUES ('9', '小亮', '英语', '60');

竖变横

select

`name` "姓名",

max(case project when '语文' THEN score ELSE 0 END) "语文",

max(case project when '数学' THEN score ELSE 0 END) "数学",

max(case project WHEN '英语' THEN score ELSE 0 END) "英语"

from score group by `name`

横变竖

创表

CREATE TABLE `student` (

`id` varchar(32) NOT NULL,

`姓名` varchar(50) NOT NULL,

`语文` int(11) DEFAULT NULL,

`数学` int(11) DEFAULT NULL,

`物理` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加数据

INSERT INTO `student1` VALUES ('0001', '小张', '93', '84', '99');

INSERT INTO `student1` VALUES ('0002', '小马', '86', '92', '90');

横变竖

select * from

(select

`姓名` 'name','语文'as'project',`语文` 'score'

from student group by `name`

UNION ALL

select

`姓名` 'name','数学'as'project',`数学` 'score'

from student

UNION ALL

select

`姓名` 'name','物理'as'project',`物理` 'score'

from student

)t order by `name`