MySQL横表与竖表互相转换

MySQL横表与竖表互相转换：

CREATE TABLE `subject`  (
  `id` int NOT NULL,
  `name` varchar(255) ,
  `yuwen` varchar(255),
  `shuxue` varchar(255),
  `yingyu` varchar(255),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

INSERT INTO `subject` VALUES (1, '小王', '89', '70', '51');
INSERT INTO `subject` VALUES (2, '小张', '69', '80', '79');
INSERT INTO `subject` VALUES (3, '小李', '80', '90', '64');

上面的语句创建的表结构如下：

id	name	yuwen	shuxue	yingyu
1	小王	89	70	51
2	小张	69	80	79
3	小李	80	90	64

要查询出下面这样的表：

name	subject	score
小张	语文	69
小张	数学	80
小张	英语	79
小李	语文	80
小李	数学	90
小李	英语	64
小王	语文	89
小王	数学	70
小王	英语	51

select *
from (select name, '语文' as subject, yuwen as score
      from `subject`
      UNION
      select name, '数学' as subject, shuxue as score
      from `subject`
      UNION
      select name, '英语' as subject, yingyu as score
      from `subject`) a
ORDER BY a.name;

接下来我们使用上面的查询结果创建新的score表，score表也就是我们刚才查出来看见的那样，现在我们将基于该表转横表。

create table score as
select *
from (select name, '语文' as subject, yuwen as score
      from `subject`
      UNION
      select name, '数学' as subject, shuxue as score
      from `subject`
      UNION
      select name, '英语' as subject, yingyu as score
      from `subject`) a
ORDER BY a.name;

我们基于该纵表转横表，最终效果如下所示：

要查询出下面这样的表结构：

名字	语文	数学	英语	总分	平均分
小王	89	70	51	210	70
小张	69	80	79	228	76
小李	80	90	64	234	78

SELECT `name`     AS                                       "姓名",
       MAX(CASE SUBJECT WHEN "语文" THEN score ELSE 0 END) 语文,
       MAX(CASE SUBJECT WHEN "数学" THEN score ELSE 0 END) 数学,
       MAX(CASE SUBJECT WHEN "英语" THEN score ELSE 0 END) 英语,
       SUM(score) as                                       总分,
       avg(score) as                                       平均分
from score
GROUP BY `name`;