drop table t1
create table t1
(
id int,
s1 int
)
Insert into t1 values(1,4)
Insert into t1 values(2,5)
Insert into t1 values(3,5)
Insert into t1 values(4,4)
Insert into t1 values(5,3)
Insert into t1 values(6,2)
Insert into t1 values(7,1)
select * from t1 order by s1 desc,id desc
--select top [PageSize] * from t1 where id not in
--(select top [PageSize-1*PageNo] id from t1 order by s1 desc,id desc)
-- order by s1 desc ,id desc
--PageSize -每页显示多少条
--PageNo -第几页
select top 3 * from t1 where id not in
(select top 3 id from t1 order by s1 desc,id desc)
order by s1 desc ,id desc
以上的超过10W条就慢了.
如果用sql2005 就简单很多.
--添加数据
Declare @i int
set @i=1
while(@i<200000)
begin
insert into t1 values(@i+1,@i)
set @i=@i+1
end
select * from
(
select ROW_NUMBER() OVER (order by s1 desc) as Pos from t1
) as T
where T.Pos >198000 and T.Pos<198050
超过20W也就1秒钟