create procedure sp_test
( @n int
)
as
begin
declare @id int, @lv int, @sec1 int , @sc1 int, @count int, @row int
declare @pre_id int, @pre_lv int, @pre_sec1 int , @pre_sc1 int
insert into #temp(lv , sec1 , sc1 )
select lv, sec1, sc1
from a
order by lv, sec1, sc1
declare cur_1 cursor for
select id, lv, sec1, sc1
from #temp
open cur_1
fetch cur_1 into @id, @lv, @sec1, @sc1
set @count=1
set @row=1
while (@@sqlstatus != 2 )
begin
if @row=1
begin
set @pre_id=@id
set @pre_lv=@lv
set @pre_sec1=@sec1
set @pre_sc1=@sc1
set @row =2
fetch cur_1 into @id, @lv, @sec1, @sc1
end
else
begin
if (@sec1 - @pre_sec1) > 100 and (@sc1 - @pre_sc1) > 200
begin
if @count <=@n
begin
insert into #return(lv, sec1,sc1) values(@lv,@sec1,@sc1)
set @count = @count+1
set @pre_id=@id
set @pre_lv=@lv
set @pre_sec1=@sec1
set @pre_sc1=@sc1
end
end
fetch cur_1 into @id, @lv, @sec1, @sc1
end
end
close cur_1
deallocate cur_1