SqlServer数据库分页

--创建测试表
CREATE TABLE [t_test]
(
[tid] INT IDENTITY(1,1) PRIMARY KEY,
              [varcharValue] VARCHAR(50),
[floatValue] FLOAT
)
--向表中插入10000条数据
declare @counter int
set @counter=1
while @counter<10000
begin
insert into [t_test] values(Convert(varchar(50),Rand()),Rand())
set @counter=@counter+1
end
go
--查询表中数据
select * from [t_test]
--显示执行时间
set statistics time on
--分页1:差集思想--没有在前20条中的前10条

select top 10 * from [t_test]  where [tid] not in(select top 20 [tid] from [t_test] order by [tid]) order by [tid]
--分页2:求前30条与前20条的差集
select top 10 * from [t_test] t1
where not exists
(
select * from (select top 20 * from [t_test] order by [tid]) t2
where t2.tid=t1.tid
)
order by tid
--分页3:取前10条从(id>前20条最大的id)
select top 10 * from [t_test] where
(
tid>(select max(tid) from (select top 20 tid from [t_test] order by tid))
)
order by tid
SELECT TOP 10 *
FROM [t_test]
WHERE ([tid] >
          (SELECT MAX([tid])
         FROM (SELECT TOP 20 [tid]
                 FROM [t_test]
                 ORDER BY [tid]) AS [t1]))
ORDER BY [tid]
--分页4:使用分析函数ROW_NUMBER()实现(只适用于2005及以上版本)

SELECT * FROM
(
      SELECT ROW_NUMBER() OVER (ORDER BY [tid]) [num],*
      FROM [t_test]
) [t1]
WHERE num>20 AND num<=30
--分页5:创建临时表:这种方案的执行效率最差,但通用性最强

CREATE TABLE [#temp1]
(
[tempId] INT IDENTITY(1,1),
[tid] INT,
[varcharValue] VARCHAR(50),
[floatValue] FLOAT
)

INSERT INTO [#temp1] SELECT * FROM [t_test] ORDER BY [tid]

SELECT * FROM [#temp1]
WHERE [tempId]>20 AND [tempId]<=30

--分页6:使用表变量
DECLARE @tt TABLE ([tempId] INT IDENTITY(1,1), [tid] INT)

INSERT INTO @tt([tid])
select  [tid] from [t_test] order by [tid]

SELECT [t1].[tid],[varcharValue],[floatValue] FROM [t_test] [t1],@tt [t2]
WHERE [tempId]>20 AND [tempId]<=30
AND [t1].[tid]=[t2].[tid]