mysql行转列 博客分类： 数据库 MySQL

准备表和数据：

CREATE TABLE `user` (

  `name` varchar(50) DEFAULT NULL,

  `subject` varchar(50) DEFAULT NULL,

  `score` int(11) DEFAULT NULL

)；

insert into user values

('zhangsan' , 'chinese' , 10),

('zhangsan' , 'math' , 20),

('zhangsan' , 'english' , 30),

('lily' , 'chinese' , 40),

('lily' , 'math' , 50),

('lily' , 'english' , 60),

('mini' , 'chinese' , 70),

('mini' , 'math' , 80),

('mini' , 'english' , 90);

 

case when 实现行转列

select name,
sum(case subject when'chinese'then score else0end)as'chinese',
sum(case subject when'math'then score else0end)as'math',
sum(case subject when'english'then score else0end)as'english'
from user
groupby name;

存储过程实现行转列

create procedure line_to_col()
begin
declare i int;
declare _chinese int;
declare _math int;
declare _english int;
declare _name varchar(10);
declare test_cursor CURSOR forselect name from user;
select count(*)into i from user;
CREATE TEMPORARY TABLE tmp_tab(
name varchar(10),
chinese_score int,
math_socre int,
english_score int);
if i>0then
open test_cursor;
repeat fetch test_cursor into _name;
select score into _chinese from user where subject ='chinese'and name =_name;
select score into _math from user where subject ='math'and name =_name;
select score into _english from user where subject ='english'and name =_name;
insert into tmp_tab values(_name,_chinese,_math,_english);
set i=i-1;
until i=0end repeat;
close test_cursor;
endif;
select DISTINCT *from tmp_tab;
drop table tmp_tab;
end

在写存储过程的时候遇到了两个问题，分别是关于游标和临时表。

因为user表中有重复的name，在设置游标时，我想直接过滤掉重复的用户，所以将游标设置成declare test_cursor CURSOR for select DISTINCT name from user;这样设置游标之后，执行存储过程，报错提示没有获取任何数据。小小同志跟我解释说，游标是遍历用的，怎么能distinct呢 ，只能对取数做distinct。

为 了拼接输出内容，我建了一个临时表，第一次调用line_to_col的时候可以正常执行，第二次调用时就报错提示tmp_tab已存在。所以在存储过程 中创建临时表，执行完后，需要及时把临时表删除掉，避免重复调用时出错。另外，小小跟我强调，临时表是放在内存里的，会耗资源，所以在用完之后需要及时删 除掉。