Mysql动态行转列（不使用存储过程）

实现思路：

动态拼接执行的SQL语句，先把要执行的语句拼在变量里，之后执行。

拼接的过程就是要动态找出数据里的所有列，然后拼成SQL

– 建表语句：
DROP TABLE IF EXISTS student_score;

CREATE TABLE student_score (
id int(8) NOT NULL,
name varchar(20) DEFAULT NULL,
project varchar(20) DEFAULT NULL,
score int(8) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*Data for the table student_score */

insert into student_score(id,name,project,score) values
(1,‘张三’,‘语文’,80),
(2,‘张三’,‘数学’,70),
(3,‘张三’,‘英语’,90),
(4,‘张三’,‘物理’,60),
(5,‘李四’,‘语文’,95),
(6,‘李四’,‘数学’,96),
(7,‘李四’,‘英语’,97),
(8,‘李四’,‘物理’,99),
(9,‘王五’,‘语文’,100),
(10,‘王五’,‘英语’,100);

– 实现语句（不需要存储过程）
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;

转载 这个 https://zhuanlan.zhihu.com/p/48843262