数据库数据行列转换。

本文以MySQL8.0数据库为例。

新建student_manage数据库，新建exam表，表中间保存学生的姓名，科目和成绩。

数据库表中间的数据如下图：

现在想将表中间的数据全部查出。但是显示的格式希望如下图：

将表中间的数据进行行列的转换。

如果已知科目只有数学，物理，化学三科。

方案一:

通过if完成

SELECT NAME,
sum(if(course='数学',scores,0)) AS 数学,
sum(if(course='物理',scores,0)) AS 物理,
sum(if(course='化学',scores,0)) AS 化学,
SUM(scores) AS 总分,
round(AVG(scores),2) AS 平均分
FROM exam GROUP BY name

按照学生姓名相同的分成一组，分别计算每组的数学成绩，物理成绩，化学成绩的总和。

如果不加sum聚合函数求和，计算的仅仅只是每组中间的第一条记录。

如果不加sum聚合函数，在其他数据库中间直接是语法错误。

方案二:

通过case完成

SELECT NAME,
SUM(case course when '数学' then scores ELSE 0 END) AS 数学,
SUM(case course when '物理' then scores ELSE 0 END) AS 物理,
SUM(case course when '化学' then scores ELSE 0 END) AS 化学,
SUM(scores) AS 总分,
round(AVG(scores),2) AS 平均分
FROM exam GROUP BY name

和if处理的思路基本一致。

如果该行科目是数学，计算该行成绩，否则，该行成绩为0。

也可以写成如下代码。

和if只是语法不同，处理思路是相同的。

如果表中间的数据科目不确定，不知道由多少行。

也就是以上两种处理方案中间的if和case出现的次数不确定。那么只能先查出表中间的所有的科目，通过科目的查询结果，来拼接if或者case的语句。

代码如下：

SET @sql_string='';
/*声明@sql_string变量  其值为空字符串*/
SELECT @sql_string:=CONCAT(@sql_string,'sum(if(course= \'',course,'\',scores,0)) as ',course, ',') AS str FROM
/*因为temp有3条记录  数学 物理 化学  所以以上的拼接语句会执行3次*/

(SELECT DISTINCT course FROM exam)AS temp ;
/*查出所有的course 科目 本例题 三个科目 数学 物理 化学  */
/*@sql_string的值为  sum(if(course='数学',scores,0)) as 数学,sum(if(course='物理',scores,0))as 物理,sum....,*/
/*会有,作为结尾*/

SET @sql_string = CONCAT('select name,',@sql_string,' sum(scores) as 总分,round(avg(scores),2) as 平均分 from exam group by name');
/*给变量@sql_string变量赋值*/
/*拼接成为一条完整的sql语句 */
/*@sql_string的值为  select name,sum(if(course='数学',scores,0)) as 数学,sum(if(course='物理',scores,0)) as .....*/
/*@sql_string是一个字符串*/

PREPARE test FROM @sql_string;/*预定义sql语句*/
EXECUTE test;/*执行sql语句*/
DEALLOCATE PREPARE test;/*释放资源*/
/*prepare execute deallocate 预处理语句*/

如果course是数学，就变成

'sum(if(course= \'数学\',scores,0)) as 数学,'