Mysql动态行转列

初始数据

期望数据

实现原理

动态拼接执行的SQL语句，先把要执行的语句拼在变量里，之后执行。
拼接的过程就是要动态找出数据里的所有列，然后拼成SQL

SET @sql = NULL;
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   'Max(IF(a.project = ''',
   a.project,
   ''', a.score, 0)) AS ''',
   a.project, ''''
  )
 ) INTO @sql
FROM student_score a;
 
SET @sql = CONCAT('Select name,', @sql, 
            ' From student_score a Group by a.name' );
PREPARE stmt FROM @sql;  -- 动态生成脚本
EXECUTE stmt;            -- 动态执行脚本
DEALLOCATE PREPARE stmt;


-- 查看拼接的 SQL
select @sql;
-- 拼接完成的语句（实际执行的脚本）
Select name,
Max(IF(a.project = '语文', a.score, 0)) AS '语文',
Max(IF(a.project = '数学', a.score, 0)) AS '数学',
Max(IF(a.project = '英语', a.score, 0)) AS '英语',
Max(IF(a.project = '物理', a.score, 0)) AS '物理' 
From student_score a Group by a.name;