mysql行转列、列转行(含实例)

mysql行转列、列转行

1.行转列

  有如图所示的表，现在希望查询的结果将行转成列(语文，数学，英文变成列)：

  使用如下查询语句实现行转列：

select id,name, max(case when course = '语文'   then score else 0 end) '语文' , max(case when course = '数学'   then score else 0 end) '数学' , max(case when course = '英语'   then score else 0 end) '英语' from student group by id,name

  效果如下：

2.列转行

  有如图所示的表，现在希望查询的结果将列成行

  使用如下查询语句实现列转行：

select id, name, '语文' as 'scrore'  FROM student2 union  select id, name, '数学' as 'scrore'  FROM student2 union  select id, name, '英语' as 'scrore'  FROM student2

  效果如下：

  所需表和数据如下：

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` int(40) NULL DEFAULT NULL,
  `course` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `student` VALUES ('12', 'tbb', 80, '语文');
INSERT INTO `student` VALUES ('12', 'tbb', 70, '数学');
INSERT INTO `student` VALUES ('12', 'tbb', 60, '英语');
INSERT INTO `student` VALUES ('13', 'xb', 98, '语文');
INSERT INTO `student` VALUES ('13', 'xb', 96, '英语');
INSERT INTO `student` VALUES ('13', 'xb', 97, '数学');
INSERT INTO `student` VALUES ('14', 'xz', 18, '语文');
INSERT INTO `student` VALUES ('14', 'xz', 28, '数学');
INSERT INTO `student` VALUES ('14', 'xz', 8, '英语');

DROP TABLE IF EXISTS `student2`;
CREATE TABLE `student2`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `语文` int(40) NULL DEFAULT NULL,
  `数学` int(40) NULL DEFAULT NULL,
  `英语` int(40) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `student2` VALUES (12, 'tbb', 78, 77, 76);
INSERT INTO `student2` VALUES (13, 'xm', 90, 91, 92);
INSERT INTO `student2` VALUES (14, 'xz', 32, 31, 30);