2、SQL server：分页

1.我的笔记：这里使用的方法为第三部分的方法四中的前两种

----------------------------------------------------------------------------------------------
--分页脚本   
写法一:
with tmpT1 as
(
select q.*,    row_number()    over    (order    by    q.ModuleName)    as    row_num    from 
(
SELECT  a.xx,   a.xxx,    b.xx,    b.xxx,   d.xx,    d.xxx
FROM    bi.xx.dbo.xxx    a
JOIN    bi.xx.dbo.xxx b    ON    a.FunctionID = b.xxxx
LEFT  JOIN    xx.dbo.xxx    c   ON    b.xxxx= c.ModuleCode
LEFT  JOIN    xx.dbo.BusiInfo   d   ON   c.PT = d.Busi_Id
where   a.xx =  'xxxx'
)q
)
select  * from tmpT1
where row_num > 0 AND row_num <= 5    

                                       --0 那个位置的参数表示需要跳过的记录数，也就是 (pageIndex - 1) * pageSize 
                                       --5 那个位置的参数表示需要查询到的最大记录数，也就是 pageIndex * pageSize
                                       --说明1：使用row_num between 1 and 5 个人感觉在代码中不是很好看，要用 (pageIndex - 1) * pageSize + 1
                                       --说明2：这里使用了临时表，用三层select嵌套是一样的(注意此时必须要在每个子查询外自定义表名如p，t等)，只不过个人觉得不太好看


写法二:
with tmpT1 as
(
select q.*,    row_number()    over    (order    by    q.ModuleName)    as    row_num    from 
(
SELECT  a.xx,   a.xxx,    b.xx,    b.xxx,   d.xx,    d.xxx
FROM    bi.xx.dbo.xxx    a
JOIN    bi.xx.dbo.xxx b    ON    a.FunctionID = b.xxxx
LEFT  JOIN    xx.dbo.xxx    c   ON    b.xxxx= c.ModuleCode
LEFT  JOIN    xx.dbo.BusiInfo   d   ON   c.PT = d.Busi_Id
where   a.xx =  'xxxx'
)q
)
select top 5 * from tmpT1
where row_num > 0                --0 那个位置的参数表示需要跳过的记录数，也就是 (pageIndex - 1) * pageSize 
                                                    --5 那个位置的参数表示需要查询的单页最大记录数，也就是 pageSize
-----------------------------------------------------------------------------------------------------

2.引言：（来自http://www.cnblogs.com/qqlin/archive/2012/11/01/2745161.html，也是一篇很好的关于分页的文章，这里觉得引言写得好）

在列表查询时由于数据量非常多，一次性查出来会非常慢，就算一次查出来了，也不能一次性显示给客户端，所以要把数据进行分批查询出来，每页显示一定量的数据，这就是数据要分页。

3.SQL Server 分页方法汇总：本部分来自：http://www.cnblogs.com/shengxincai/p/6097588.html

PageSize = 30

PageNumber = 201

方法一：(最常用的分页代码， top / not in)

select top 30 UserId from UserInfo where UserId not in (select top 6000 UserId from UserInfo order by UserId) order by UserId

备注： 注意前后的order by 一致

方法二：(not exists， not in 的另一种写法而已)

select top 30 * from UserLog where not exists (select 1 from (select top 6000 LogId from UserLog order by LogId) a where a.LogId = UserLog.LogId) order by LogId

备注：EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。此处的 select 1 from 也可以是select 2 from，select LogId from, select * from 等等，不影响查询。而且select 1 效率最高，不用查字典表。效率值比较：1 > anycol > *

方法三：(top / max， 局限于使用可比较列排序的时候)

select top 30 * from UserLog where LogId > (select max(LogId) from (select top 6000 LogId from UserLog order by LogId) a ) order by LogId

备注：这里max()函数也可以用于文本列，文本列的比较会根据字母顺序排列，数字 < 字母（无视大小写） < 中文字符

方法四：(row_number() over (order by LogId))

select top 30 * from ( select row_number() over (order by LogId) as rownumber,* from UserLog)a
where rownumber > 6000 order by LogId

select * from (select row_number()over(order by LogId) as rownumber,* from UserLog)a
where rownumber > 6000 and rownumber < 6030 order by LogId

select * from (select row_number()over(order by LogId) as rownumber,* from UserLog)a
where rownumber between 6000 and  6030 order by LogId

 

select *
from (
    select row_number()over(order by tempColumn)rownumber,*
    from (select top 6030 tempColumn=0,* from UserLog where 1=1 order by LogId)a
)b
where rownumber>6000

row_number() 的变体，不基于已有字段产生记录序号，先按条件筛选以及排好序，再在结果集上给一常量列用于产生记录序号
以上几种方法参考http://www.cnblogs.com/songjianpin/articles/3489050.html

备注:  这里rownumber方法属于排名开窗函数（sum, min, avg等属于聚合开窗函数，ORACLE中叫分析函数，参考文章：SQL SERVER 开窗函数简介 )的一种，搭配over关键字使用。

方法五：(offset /fetch next, SQL Server 2012支持)

select * from UserLog Order by LogId offset 6000 rows fetch next 30 rows only

备注： 性能参考文章《SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试》

 

参考文档：

1、http://blog.csdn.net/qiaqia609/article/details/41445233

2、http://www.cnblogs.com/songjianpin/articles/3489050.html

3、http://database.51cto.com/art/201108/283399.htm