MS Sql Server 中使用 MySQL 中的 limit 分页功能
浏览数：292 /
时间：2015年09月04日
子查询得到逆序排列的前PageIndex*PageSize条记录，然后把这PageIndex*PageSize条记录按正序排列得到前PageSize条记录。ＯＫ了，这里的前PageSize条记录，实际上就是我们想要的记录。下面是一个具体的例子：
注：其中的PageSize（分页的大小）和PageIndex（分页索引，初始假设为0）均为变量，
方法1:
SELECT * FROM (SELECT TOP PageSize * FROM (SELECT TOP [PageIndex*PageSize] * FROM table ORDER BY id asc) as b ORDER BY id desc) as c ORDER BY id asc
方法2(推荐):
SELECT * from (SELECT row_number() over (order by id asc) as rownumber, * FROM tAvctoun WHERE t_world = 0 and tdate between '2012-11-1' and '2012-11-20') as t Where t.rownumber > 15 And t.rownumber <= 20
方法3(带group by):
SELECT * FROM (SELECT row_number() over (order by convert(varchar(10),dDate,120) asc) as rownumber,count(nUserNo) as persons,convert(varchar(10),dDate,120) as dDate FROM tUser GROUP BY convert(varchar(10),dDate,120) HAVING convert(varchar(10),dDate,120) BETWEEN '2011-11-24' and '2011-11-27') as t where t.rownumber > 0 and t.rownumber <=5
(group by 中 用函数查出来的列需要指定别名 不然会报错)
注：其中的PageSize（分页的大小）和PageIndex（分页索引，初始假设为0）均为变量，
方法1:
SELECT * FROM (SELECT TOP PageSize * FROM (SELECT TOP [PageIndex*PageSize] * FROM table ORDER BY id asc) as b ORDER BY id desc) as c ORDER BY id asc
方法2(推荐):
SELECT * from (SELECT row_number() over (order by id asc) as rownumber, * FROM tAvctoun WHERE t_world = 0 and tdate between '2012-11-1' and '2012-11-20') as t Where t.rownumber > 15 And t.rownumber <= 20
方法3(带group by):
SELECT * FROM (SELECT row_number() over (order by convert(varchar(10),dDate,120) asc) as rownumber,count(nUserNo) as persons,convert(varchar(10),dDate,120) as dDate FROM tUser GROUP BY convert(varchar(10),dDate,120) HAVING convert(varchar(10),dDate,120) BETWEEN '2011-11-24' and '2011-11-27') as t where t.rownumber > 0 and t.rownumber <=5
(group by 中 用函数查出来的列需要指定别名 不然会报错)
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
