以下语句取在以FID降序排列的情况下的第2到4条记录
select * from
(SELECT ROW_NUMBER() OVER(ORDER BY FID DESC) AS rownum ,FID
FROM dbo.[file]
)
as a
where a.rownum>=2 and a.rownum<=4
以前写法存储过程的分页方法如下:
@RecordsInPerPage --每页的记录数
@PageNo --当前页号
如下示例,正确替换@RecordsInPerPage和@PageNo就可方便的取得对应页的记录,效率很好
select TOP @RecordsInPerPage from Employee
and EmpID not in
(select TOP @RecordsInPerPage*(@PageNo-1) EmpID
where
DepId='45'
order by EmpID
)
order by EmpID