SQLSERVER常用的三种分页查询及对比

1. top+嵌套查询
2. ROW_NUMBER()开窗函数(支持SqlServer 2005版本以上)
3. offset fetch(SqlServer 2012 版本以上)

top+嵌套查询

?

1	select top 10 * from BigDataTable where id not in(select top 20 id from BigDataTable order by id) order by id

　　注意：两个排序字段要保持一致!

ROW_NUMBER()开窗函数

?

1 2 3 4

create view View_Page as select *,ROW_NUMBER()over(order by id) as rowindex from BigDataTable SELECT * FROM View_Page WHERE rowindex>=21 AND rowindex<=30

offset fetch

?

1	SELECT * FROM BigDataTable ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

　　注意：一定要排序!

效率对比

    我们用了一个含有1028135条数据的数据库，分别使用上述三种SQL来取得1000001-1000020条数据来进行对比

1.使用top+嵌套查询执行结果如下:

   取十次查询时间求平均值:（1.023+0.988+0.949+0.960+1.028+1.002+0.957+0.956+0.961+0.964）/10 =0.9788s

2.使用ROW_NUMBER()开窗函数执行结果如下:

   取十次查询时间求平均值:（0.839+0.841+0.845+0.840+0.853+0.845+0.828+0.861+0.874+0.861）/ 10=0.8487s

3.offset fetch执行结果如下:

  取十次查询时间求平均值:（0.356+0.358+0.322+0.322+0.350+0.325+0.355+0.315+0.328+0.330）/ 10=0.3361s

注:这里进行的对比这是大概结论呢，因为我们没有排除以主键,聚集索引作为排序字段带来的影响。

从上可以大致得出，效率:top+嵌套查询<ROW_NUMBER()<offset fetch

实战

我们需要分组并排序，相同项目的数据要在一起，然后分页并得到总数量对接前端分页展示

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

WITH CTE AS (     SELECT         pd.*,         p.project_number, p.project_name,         ROW_NUMBER() OVER (PARTITION BY pd.project_id ORDER BY pd.create_date desc) AS group_row_num     FROM         project_detail pd left join project p on pd.project_id = p.project_id     WHERE         pd.parent_id is null ) SELECT     * FROM     (         SELECT             *,             ROW_NUMBER() OVER (ORDER BY project_id) AS row_num         FROM             CTE     ) AS grouped ORDER BY row_num OFFSET #{pageNum} ROWS FETCH NEXT #{pageSize} ROWS ONLY

　　

?

1 2 3 4 5 6

SELECT         count(*)     FROM         project_detail pd left join project p on pd.project_id = p.project_id     WHERE         pd.parent_id is null

　　注意：#{}中是你需要传入的参数，where中的条件可以自行修改添加，表为测试随机命名，可以自行修改

参考原文地址：https://www.freesion.com/article/3110333763/