MySQL行转列与列转行，存储过程动态行转列

一、创建表插入一些示例数据

create table score(
	id bigint(20) auto_increment,
	sname varchar(255) comment "学生姓名",
	cname varchar(255) comment "课程名称",
	score int(11) comment "分数",
	primary key(id)
)engine=innodb;

insert into score values(1,"小孙","mysql",55);
insert into score values(2,"小孙","python",66);
insert into score values(3,"小孙","java",47);
insert into score values(4,"小郭","mysql",70);
insert into score values(5,"小郭","python",85);
insert into score values(6,"小林","java",67);
insert into score values(7,"小林","mysql",77);

查看下插入的数据

二、行转列

1. 概述、当学生人数较多时，不能更清晰的看出学生各个课程的成绩对比，进行行转列来看一哈。

方法1：

select sname,max(case cname when "java" then score end) java,
				max(case cname when "python" then score end) python,
					max(case cname when "mysql" then score end) mysql from score group by sname;

方法2：

select distinct a.sname,
(select score from score b where a.sname = b.sname and b.cname = "mysql") as mysql,
(select score from score b where a.sname = b.sname and b.cname = "python") as python,
(select score from score b where a.sname = b.sname and b.cname = "java") as java
from score a;

方法3：

运用存储过程动态的进行行转列

好处：

1. 不需要每次添加课程来调整SQL语句

2. 随时可以用call 函数来调用

3.较快的执行速度（在首次运行一个存储过程时，优化器对其进行分析，而批处理每次运行进行编译和优化，速度相对较慢）

4.通过存储过程的权限进行限制，能够实现对相应的数据访问权限限制，避免非授权用户数据的访问，保证数据的安全性。

drop procedure if exists sp_score;
delimiter &&  # 定义&& 为SQL结束语
create procedure sp_score()
begin
	# 课程名称
	declare cname_n varchar(20) ; # 定义课程字段cname_n
	declare count_ int ; # 定义循环条件（课程总量）
	declare i int default 0 ; # 拼接SQL字符串
	set @s = 'select sname' ; # 定义s变量为字符串前缀
	set count_ = (select count(distinct cname) from score); # 给count_赋值
	while i < count_ do
		set cname_n = ( 
			select cname from score group by cname limit i,1); # cname_n的值依次是每个课程名称
		set @s = concat(@s,", sum(case cname when ",'\"',cname_n,'\"'," then score else 0 end)"," as ",'\"',cname_n,'\"');
		select @s;
		set i = i +1;
		end
	while;
	set @s = concat(@s,' from score group by sname'); # 用于调试 select @s;
	PREPARE stmt
	FROM
		@s ; EXECUTE stmt ;
	END&&
delimiter ; # 将结束语调整为 ;
call sp_socre();

三、MySQL列转行

1. 创建表插入刚刚得出的数据进行列转行。

create table cname

	select sname,max(case cname when "java" then score end) java,
				max(case cname when "python" then score end) python,
					max(case cname when "mysql" then score end) mysql from score group by sname

2. 查出一门课程的所有学生成绩

select sname,"java" cname,java as score from cname;

3. 将所有学生的所有课程成绩列转行用union连接

select sname,"java" cname,java as score from cname
union select sname,"python" cname,python as score from cname
union select sname,"mysql" cname,mysql as score from cname
order by sname,cname;

4. 将上述结果集有NULL的值去掉

select * from (select sname,"java" cname,java as score from cname
union select sname,"python" cname,python as score from cname
union select sname,"mysql" cname,mysql as score from cname
order by sname,cname)a 
where a.score is not null;