with t1 as ( select 'A' f1, 4 f2 from dual union all select 'B' f1, 8 f2 from dual ) select t1.f1,1 f2 from t1, (select rownum r1 from dual connect by rownum < 100) t2 where t1.f2 >= t2.r1 order by t1.f1
使用游标来实现,没有测试过。declare @tb table(f1 varchar(10),f2 int) declare @f1 varchar(10),@f2 int declare cur_tmp cursor fast_forward for select f1,f2 from t1 open cur_tmp fetch next from cur_tmp into @f1,@f2 while @@fetch_status = 0 begin while @f2 > 0 begin insert into @tb select @f1,1 set @f2 = @f2 - 1 end fetch next from cur_tmp into @f1,@f2 end select * from @tb;
(
select 'A' f1, 4 f2 from dual
union all
select 'B' f1, 8 f2 from dual
)
select t1.f1,1 f2
from t1,
(select rownum r1 from dual
connect by rownum < 100) t2
where t1.f2 >= t2.r1
order by t1.f1
declare @f1 varchar(10),@f2 int
declare cur_tmp cursor fast_forward
for select f1,f2 from t1
open cur_tmp
fetch next from cur_tmp
into @f1,@f2
while @@fetch_status = 0
begin
while @f2 > 0
begin
insert into @tb select @f1,1
set @f2 = @f2 - 1
end
fetch next from cur_tmp into @f1,@f2
end
select * from @tb;