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

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

top+嵌套查询

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

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

ROW_NUMBER()开窗函数

  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

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

实战

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

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

　　

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/