早上有人求助，我写出答案如下，觉得这个问题还算有代表性：
不过MYSQL的预处理好像得用动态变量，静态的不认。老出错。
------------------
表[A],2个字段，name,num,
数据为：
name num
tom 3
jerry 2
jessic 2
lily 1
希望通过一段SQL语句得到以下的数据集：
tom
tom
tom
jerry
jerry
jessic
jessic
lily
就是根据NUM的值，重复几次NAME。
--------------------------------------
贴出完成版
create table test(name varchar(64),num int(11));
insert into test values('tom',3),('jerry',2),('jessic',2),('lily',1);
---------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_test`$$
CREATE PROCEDURE `test`.`sp_test`()
BEGIN
declare cnt int(11);
declare i int(11);
declare j int(11);
create temporary table num (name varchar(64),num int(11));
select count(*) from test where 1 = 1 into cnt;
set i = 0;
loop1:loop
set @query1 = concat('select name,num from test limit ',i,',1 into @name,@num1');
prepare stmt1 from @query1;
execute stmt1;
deallocate prepare stmt1;
set j = 1;
loop2:loop
set @query2 = 'insert into num values(?,?)';
prepare stmt2 from @query2;
execute stmt2 using @name,@num1;
deallocate prepare stmt2;
set j = j + 1;
if j > @num1 then
leave loop2;
end if;
end loop loop2;
set i = i + 1;
if i > cnt -1 then
leave loop1;
end if;
end loop loop1;
select name from num;
END$$
DELIMITER ;
------------------
call sp_test();
query result(8 records)
name
tom
tom
tom
jerry
jerry
jessic
jessic
不过MYSQL的预处理好像得用动态变量，静态的不认。老出错。
------------------
表[A],2个字段，name,num,
数据为：
name num
tom 3
jerry 2
jessic 2
lily 1
希望通过一段SQL语句得到以下的数据集：
tom
tom
tom
jerry
jerry
jessic
jessic
lily
就是根据NUM的值，重复几次NAME。
--------------------------------------
贴出完成版
create table test(name varchar(64),num int(11));
insert into test values('tom',3),('jerry',2),('jessic',2),('lily',1);
---------------------------
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_test`$$
CREATE PROCEDURE `test`.`sp_test`()
BEGIN
declare cnt int(11);
declare i int(11);
declare j int(11);
create temporary table num (name varchar(64),num int(11));
select count(*) from test where 1 = 1 into cnt;
set i = 0;
loop1:loop
set @query1 = concat('select name,num from test limit ',i,',1 into @name,@num1');
prepare stmt1 from @query1;
execute stmt1;
deallocate prepare stmt1;
set j = 1;
loop2:loop
set @query2 = 'insert into num values(?,?)';
prepare stmt2 from @query2;
execute stmt2 using @name,@num1;
deallocate prepare stmt2;
set j = j + 1;
if j > @num1 then
leave loop2;
end if;
end loop loop2;
set i = i + 1;
if i > cnt -1 then
leave loop1;
end if;
end loop loop1;
select name from num;
END$$
DELIMITER ;
------------------
call sp_test();
query result(8 records)
name
tom
tom
tom
jerry
jerry
jessic
jessic
lily
本文转自 david_yeung 51CTO博客，原文链接：http://blog.51cto.com/yueliangdao0608/81398，如需转载请自行联系原作者
