初始数据
期望数据
实现原理
动态拼接执行的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;