MySQL数据库如何进行表数据行列转换，指定格式汇总？

最新推荐文章于 2023-03-15 16:53:57 发布

原创最新推荐文章于 2023-03-15 16:53:57 发布 · 756 阅读

2 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

20 篇文章

订阅专栏

本文介绍如何使用SQL将一张包含学员姓名、学科及分数的数据表，转换为目标格式，展示各学员不同学科的成绩及总分，同时提供合计行。通过CASE、SUM、GROUP BY和WITH ROLLUP等函数和关键字，实现数据的聚合与展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

应用场景：现在有一张数据表data_table，包含了id（编号）、name（姓名）、subject(学科)、score（分数），每个学员有三门学科 Java基础、数据库、linux运维，并且有相应的分数，如下面的测试表数据，现在要转化成目标表的数据格式，用sql怎么实现？

测试表数据

mysql> select * from data_table;
+----+--------+-----------+-------+
| id | name   | subject   | score |
+----+--------+-----------+-------+
|  1 | 林大侠  | Java基础   |    99 |
|  2 | 林大侠  | 数据库     |    88 |
|  3 | 林大侠  | linux运维  |    77 |
|  4 | 李大象  | Java基础   |    89 |
|  5 | 李大象  | 数据库     |    69 |
|  6 | 李大象  | linux运维  |    87 |
|  7 | 周汪汪  | Java基础   |    96 |
|  8 | 周汪汪  | 数据库     |    85 |
|  9 | 周汪汪  | linux运维  |    59 |
+----+--------+-----------+-------+
9 rows in set (0.00 sec)

目标表数据

+--------+----------+--------+-----------+------+
| 姓名   | Java基础  | 数据库  | linux运维 | 总分    |
+--------+----------+--------+-----------+------+
| 周汪汪  |       96 |     85 |    59    |  240 |
| 李大象  |       89 |     69 |    87    |  245 |
| 林大侠  |       99 |     88 |    77    |  264 |
| 合计    |      284 |    242 |   223    |  749 |
+--------+----------+--------+-----------+------+
4 rows in set (0.00 sec)

正确答案

 SELECT IFNULL(`name`,'合计') AS '姓名',
 SUM(CASE WHEN SUBJECT ='Java基础' THEN score END) AS 'Java基础',
 SUM(CASE WHEN SUBJECT ='数据库' THEN score END) AS '数据库',
 SUM(CASE WHEN SUBJECT ='linux运维' THEN score END) AS 'linux运维',
 SUM(score) AS '总分'
 FROM data_table
 GROUP BY `name`
 WITH ROLLUP;

答案分析

首先看目标表数据，我们知道表属性包括姓名、Java基础、数据库、linux运维、总分五个字段，那么我的sql语句在select时应该包括五个字段，其中总分项在进行分组后sum求和就行！本sql实现的重点在于分别对学科（Java基础、数据库、linux运维）总计，这时我们就要用with rollup修饰符进行统计额外的汇总信息，当统计完了之后，姓名列的最后一个是NULL,怎么办了？所以我们还得应用了IFNULL()进行判断，不为NULL则查询出存在的姓名，为NULL则手动命名为“合计”，这样的符合目标表数据的查询结果。这个应用在面试中也是高频出现的sql题型，需要的手写sql能力和sql的基础功能。

•MySQL数据库流程控制(单行)函数#if、ifnull、case等函数总结