declare @SQLStr varchar(8000)set @SQLStr='SELECT Top '+cast(@每页大小 as varchar)+' * FROM T WHERE SortField NOT IN (SELECT TOP '+cast(@每页大小*@第几页 as varchar)+' SortField from T )'exec(@SQLStr)
select identity(int,1,1) newid,* into #temp from table select top 20 - 10 * from #temp where newid >=10 drop table #temp
知其然还要知其所以然,以下我替你总结了一下方法:如何取出第m条到第n条记录间的纪录? 方法一:取出前n条和前m条然后取出其差(n-m) select top(n-m) * from table where 关键字段 not in(select top m 关键字段 from table)方法二:先取出前n条的,再倒过来取出n-m就可以 select top(n-m+1) * from (select top n * from table order by 关键字段 desc) order by 关键字段
select * from (select top 20 * from table order by id) a where id not in (select top 9 id from table order by id)select top 10 * from #temp where id not in (select top 20 id from #temp)
TO:小马哥 先取出前n条的,再倒过来取出n-m就可以的方法有问题,结果取出来的是倒数10-20条记录; 如下方法,取出第10条到第20条记录,测试过,没问题 select top 11 * from ( select top 11 * from ( select Top 20 * from ( select top 20 * from MLOther ) A order by FieldName desc ) B ) C order by FieldName不过,还是觉得用临时表的方法比较好!!
select top 20 - 10 * from #temp where newid >=10
drop table #temp
方法一:取出前n条和前m条然后取出其差(n-m)
select top(n-m) *
from table
where 关键字段 not in(select top m 关键字段 from table)方法二:先取出前n条的,再倒过来取出n-m就可以
select top(n-m+1) *
from (select top n * from table order by 关键字段 desc)
order by 关键字段
先取出前n条的,再倒过来取出n-m就可以的方法有问题,结果取出来的是倒数10-20条记录;
如下方法,取出第10条到第20条记录,测试过,没问题
select top 11 * from (
select top 11 *
from (
select Top 20 * from ( select top 20 * from MLOther ) A
order by FieldName desc
) B
) C order by FieldName不过,还是觉得用临时表的方法比较好!!
我的方法没问题,你可以测试一下