MySQL行转列

原文地址：http://blog.163.com/weizi_mm/blog/static/31554420122544629224/

准备表和数据：

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 else 0 end)  as 'chinese',
sum( case subject when 'math' then score else 0 end) as 'math',
sum( case subject when 'english' then score else 0 end) as 'english'
from user
group by 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 for select 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> 0 then
    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=0 end repeat;
    close test_cursor;
  end if;
  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已存在。所以在存储过程中创建临时表，执行完后，需要及时把临时表删除掉，避免重复调用时出错。另外，小小跟我强调，临时表是放在内存里的，会耗资源，所以在用完之后需要及时删除掉。