这样就可以了。DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`sp_left_jion`$$CREATE PROCEDURE `test`.`sp_left_jion`()BEGIN set @stmt = concat('select * from table1 a left join ',substring('a.name',locate('.','a.name')+1),' b on a.id=b.id'); prepare s1 from @stmt; execute s1; deallocate prepare s1;END$$DELIMITER ;
算了还是给你写了吧。DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`sp_left_jion`$$CREATE PROCEDURE `test`.`sp_left_jion`()BEGIN declare i int default 0; declare cnt int default 0; select count(name) from table1 into cnt;
while i<cnt do set @stmt1 = concat('select a.name from table1 a limit ',i,'1 into @name_c'); prepare s1 from @stmt1; execute s1; deallocate prepare s1; set @stmt2 = concat('select * from table1 a left join ',substring(@name_c,locate('.',@name_c)+1),' b on a.id=b.id'); prepare s2 from @stmt2; execute s2; deallocate prepare s2; set i = i + 1; end while;END$$DELIMITER ;
set @stmt = concat('select * from table1 a left join ',substring('a.name',locate('.','a.name')+1),' b on a.id=b.id');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;END$$DELIMITER ;
先一次一个记录取出a.name的记录,然后替换掉我写的存储过程里的a.name就可以了。
http://blog.chinaunix.net/u/29134/showart_402376.html
declare i int default 0;
declare cnt int default 0;
select count(name) from table1 into cnt;
while i<cnt do
set @stmt1 = concat('select a.name from table1 a limit ',i,'1 into @name_c');
prepare s1 from @stmt1;
execute s1;
deallocate prepare s1;
set @stmt2 = concat('select * from table1 a left join ',substring(@name_c,locate('.',@name_c)+1),' b on a.id=b.id');
prepare s2 from @stmt2;
execute s2;
deallocate prepare s2;
set i = i + 1;
end while;END$$DELIMITER ;