select top 1 * from test where id not in ( select top 99 id from test order by id ) order by id ;select top 101 * from test where id not in ( select top 99 id from test order by id ) order by id ; id是表test的查询索引字段
select a.id from test as a join test as b on (a.id>=b.id) group by a.id having count(*)=100 注释同前
select Top 201 IDENTITY(int,1,1) as Nbr,* INTO #tmp From tablename order by ID --第100條記錄 Select * From #tmp where Nbr=100 --第100-200條記錄。 Select * From #tmp where Nbr Between 100 and 200drop table #tmp
但一定要用 not in 吗?他很慢呀? 且没有查询索引字段怎么办?
from test as a
join
test as b on (a.id>=b.id)
group by a.id
having count(*)=100
注释同前
--第100條記錄
Select * From #tmp where Nbr=100
--第100-200條記錄。
Select * From #tmp where Nbr Between 100 and 200drop table #tmp