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