MSSQL2008中分页查询的实现

在后台管理中,涉及分页操作,需要数据库支持limit和offset选项,但在MSSQL2008中这两个选项不支持,不过可以用ROW_NUMBER函数在SQL语句中实现数据分页效果。SQL代码示例如下:

1
2
3
4
5
6
SELECT * FROM  
(SELECT Users.id as id,name,sign,account,audit,roleName,RoleId, Users.deleteFlag as deleteFlag,
ROW_NUMBER() OVER (ORDER BY Users.createdAt) AS Seq
FROM Users LEFT JOIN Roles ON Users.RoleId=Roles.id)Users
Where deleteFlag=0 and
Seq > :offset_num and Seq<= :offset_num+:limit_num

参考链接

  1. How to use Offset . . .Fetch Next in SQL SERVER 2008 R2,by microsoft msdn.