写了一个MYSQL的行转列的存储过程 zz

http://blog.chinaunix.net/u/29134/showart_364659.html

 

网上的都是一些静态的,用CASE WHEN结构实现。所以我写了一个动态的。




SP 代码:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_row_column_wrap`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_row_column_wrap`(IN $schema_name varchar(64),
IN $table_name varchar(64))
BEGIN
  declare cnt int(11);
  declare $table_rows int(11);
  declare i int(11);
  declare j int(11);
  declare s int(11);
  declare str varchar(255);
  -- Get the column number of the table
  select count(1) from information_schema.columns where table_schema=$schema_name and table_name=$table_name into cnt;
  -- Get the row number of the table
  select table_rows from information_schema.tables where table_schema = $schema_name and table_name=$table_name into $table_rows;
  -- Check whether the table exists or not
  drop table if exists test.temp;
  create table if not exists test.temp (`1` varchar(255) not null);
  -- loop1 start
  set i = 0;
  loop1:loop
    if i = $table_rows-1 then
      leave loop1;
    end if;
    set @stmt1 = concat('alter table test.temp add `',i+2,'` varchar(255) not null');
    prepare s1 from @stmt1;
    execute s1;
    deallocate prepare s1;
    set @stmt1 = '';
    set i = i + 1;
  end loop loop1;
  -- loop1 end;
  set s = 0;
  -- loop2 start
  loop2:loop
  -- leave loop2
    if s=cnt then
      leave loop2;
    end if;
    set @stmt2 = concat('select column_name from information_schema.columns where table_schema="',$schema_name,
                        '" and table_name="',$table_name,'" limit ',s,',1 into @temp;');
    prepare s2 from @stmt2;
    execute s2;
    deallocate prepare s2;
    set @stmt2 = '';
    set j=0;
    set str = ' select ';
    -- Loop3 start
    loop3:loop
      if j = $table_rows then
        leave loop3;
      end if;
      set @stmt3 = concat('select ',@temp,' from ',$schema_name,'.',$table_name,' limit ',j,',1 into @temp2;');
      prepare s3 from @stmt3;
      execute s3;
      set str = concat(str,'"',@temp2,'"',',');
      deallocate prepare s3;
      set @stmt3 = '';
      set j = j+1;
    end loop loop3;
    set str = left(str,length(str)-1);
    -- insert new data into table
    set @stmt4 = concat('insert into test.temp',str,';');
    prepare s4 from @stmt4;
    execute s4;
    deallocate prepare s4;
    set @stmt4 = '';
    set s=s+1;
  end loop loop2;
END$$

DELIMITER ;




以下是测试结果:
======
select * from a;
select * from b;
select * from salary;

call sp_row_column_wrap('test','a');
select * from test.temp;
call sp_row_column_wrap('test','b');
select * from test.temp;
call sp_row_column_wrap('test','salary');
select * from test.temp;


query result(2 records)

aidtitle
1111
2222

query result(3 records)

bidaidimagetime
121.gif2007-08-08
222.gif2007-08-09
323.gif2007-08-08

query result(7 records)

idcostdesAutoid
110aaaa1
115bbbb2
120cccc3
280aaaa4
2100bbbb5
260dddd6
3500dddd7

query result(2 records)

12
12
111222

query result(4 records)

123
123
222
1.gif2.gif3.gif
2007-08-082007-08-092007-08-08


query result(4 records)

1234567
1112223
1015208010060500
aaaabbbbccccaaaabbbbdddddddd
1234567

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值