分页查询语句

前面几项简单分页查询语句适用于普通数据库

MSSQL可以使用存储过程，ORACLE可使用强大的查询语句（有空再补上了）

1.初始思路（性能最差的）

select top 10 * from table where id not in (select top 5000 id from table)

2.进一步改造

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 

3.继续优化

select top 10 * from people where id>(select max(id) from(select top 5000id from people order by id)) 

4.再再优化

 select * from (SELECT top 10 * FROM (select top 5000 * from people )order by id desc ) order by id ; 

5.MSSQL2005-2008

declare @PageNumber [int]=2 --第几页
declare @PageSize [int]=5 --每页大小
declare @START_ID [int] 
declare @END_ID [int] 
SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)

 SELECT *   FROM (SELECT ROW_NUMBER() OVER(ORDER BY [UserID]) AS rownum, 
		 [UserID],[RealName]
		  FROM [dbo].[UserInfo]  where Userid>1 ) AS D
	   Where rownum >= @START_ID AND  rownum <= @END_ID   ORDER BY [UserID]