CREATE TABLE `value_test` ( `id` int(11) NOT NULL auto_increment, `value` varchar(4) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 insert into value_test values('','AA'); insert into value_test values('','BB'); insert into value_test values('','CC'); insert into value_test values('','DD'); insert into value_test values('','EE'); insert into value_test values('','FF');-====================================- ====================== DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`sp_value_test`$$CREATE PROCEDURE `test`.`sp_value_test`() BEGIN DECLARE cnt int; DECLARE i int; SELECT count(value) FROM value_test INTO cnt; SET i = 0; loop1:LOOP SET @stmt1 = "select value from value_test where id%2 = 0 limit "; SET @stmt1 = concat(@stmt1,i,",1 into @str1;"); PREPARE s1 FROM @stmt1; EXECUTE s1; DEALLOCATE PREPARE s1; SET @stmt2 = "select value from value_test where id%2 = 1 limit "; SET @stmt2 = concat(@stmt2,i,",1 into @str2;"); PREPARE s1 FROM @stmt2; EXECUTE s1; DEALLOCATE PREPARE s1; SELECT concat(@str1,@str2); SET i = i + 1; IF i = cnt/2 THEN leave loop1; END IF; END LOOP loop1;
`id` int(11) NOT NULL auto_increment,
`value` varchar(4) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into value_test values('','AA');
insert into value_test values('','BB');
insert into value_test values('','CC');
insert into value_test values('','DD');
insert into value_test values('','EE');
insert into value_test values('','FF');-====================================-
======================
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`sp_value_test`$$CREATE PROCEDURE `test`.`sp_value_test`()
BEGIN
DECLARE cnt int;
DECLARE i int;
SELECT count(value) FROM value_test INTO cnt;
SET i = 0;
loop1:LOOP
SET @stmt1 = "select value from value_test where id%2 = 0 limit ";
SET @stmt1 = concat(@stmt1,i,",1 into @str1;");
PREPARE s1 FROM @stmt1;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @stmt2 = "select value from value_test where id%2 = 1 limit ";
SET @stmt2 = concat(@stmt2,i,",1 into @str2;");
PREPARE s1 FROM @stmt2;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT concat(@str1,@str2);
SET i = i + 1;
IF i = cnt/2 THEN
leave loop1;
END IF;
END LOOP loop1;
END$$DELIMITER ;
====================
call sp_value_test();
结果:
BBAA
DDCC
FFEE