本文为刘兴原创, 如果转载,请注明原网址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();