前面几项简单分页查询语句适用于普通数据库
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]