侧边栏壁纸
博主头像
樯哥的技术分享网博主等级

学无止境,学以致用,志存高远。

  • 累计撰写 17 篇文章
  • 累计创建 10 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

MySQL横表与竖表互相转换

随心
2023-06-24 / 0 评论 / 0 点赞 / 50 阅读 / 8300 字

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

0

评论区