筛选第m到n条记录

/*
filter records with positions from M to N
*/

create table #test (val char( 2))
insert #test select 'a1'
insert #test select 'a2'
insert #test select 'a3'
insert #test select 'a4'
insert #test select 'a5'
insert #test select 'a6'
insert #test select 'a7'
insert #test select 'a8'
insert #test select 'a9'

declare @m int, @n int
select @m = 5, @n = 7

-- method 1: select top n-m from (select top n from ..)
declare @sql nvarchar( 1000)
select @sql = 'select * from (select top 1 * from (select top 9 * from #test) t order by val desc) tab order by val asc'
select @sql = replace(@sql, '1', cast(@n-@m+ 1 as varchar))
select @sql = replace(@sql, '9', cast(@n as varchar))
exec(@sql)

-- method 2: find the value at both end
declare @start_val char( 2), @end_val char( 2), @i int
select @start_val = '', @end_val = '', @i = 1
select
    @start_val = case when @i = @m then val else @start_val end,
    @end_val = case when @i = @n then val else @end_val end,
    @i = @i + 1
from #test
select * from #test where val >= @start_val and val <= @end_val

-- clean
drop table #test