mysql-行转列

本文为刘兴原创， 如果转载，请注明原网址http://deepfuture.iteye.com/blog/697154

drop PROCEDURE IF EXISTS gxtj;
Delimiter ^^
CREATE PROCEDURE gxtj()
BEGIN
 declare  tmpxm varchar(20) default '' ;
 declare  allsql varchar(1000) default '';
 declare cur_xm cursor for select khxm.name  from khxm ;
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET tmpxm = null;
 open cur_xm;
 fetch cur_xm into tmpxm;
        WHILE ( tmpxm  is not null) DO
              set allsql  =CONCAT(allsql,"MAX(CASE WHEN khxm.name ='",tmpxm ,"' THEN khzl.fenshu ELSE 0 END) AS ", tmpxm,","); 
            FETCH  cur_xm INTO tmpxm;
        END WHILE;
 close cur_xm;
        set allsql=CONCAT("select * from (select  people.name as pname,keshi.name as kname,people.bm  from people,keshi where  people.bm=keshi.bm) as p left join (select  ",allsql,"khzl.year,khzl.time,khzl.peopleid  FROM khzl, khxm WHERE khzl.khbm = khxm.bm) as zl on p.bm=zl.peopleid");
        set @sql=allsql;
        prepare sqlxm  from  @sql;
        execute sqlxm;
        deallocate prepare sqlxm;
END^^
Delimiter ;
call gxtj();

 

评论

1 楼 步青龙 2011-12-08  

mysql 竟然这么麻烦 ，这个功能在Oracle中一个wm_concat()搞定