分页写法参考

随着SQL Server版本的升级，常用的方法有三种：TOP，ROW_NUMBER，OFFSET/FETCH NEXT。

一. TOP

(1) 利用order by正反排序

declare @page_no int
declare @page_size int

select * 
from (select top @page_size * 
from (select top @page_size*@page_no * from split_pages order by ID) a 
order by ID desc) b 
order by ID

做完最里层select后，再对派生表查询时，index就没有效果了，而且越往后面要top更多的数据，这种写法会更慢。

(2) 利用NOT IN或者NOT EXISTS

declare @page_no int
declare @page_size int

select top @page_size *
from split_pages
where ID NOT IN (select top @page_size*(@page_no-1) ID from split_pages order by ID)
order by ID

通常在写SQL语句时，用IN/EXISTS不一样，如果逻辑不变的话， EXISTS的效率高。

不过，利用NOT IN分页，和用NOT EXISTS效果基本一样，因为都需要扫完全部数据。

(3) 利用ID大于MAX(ID)

declare @page_no int
declare @page_size int

select top @page_size *
from split_pages
where ID > (select MAX(ID) from (select top @page_size*(@page_no-1) ID from split_pages order by ID) t)
order by ID

在使用TOP分页时，这种用法效率最高。

二. ROW_NUMBER
SQL Server 2005开始的新语法，和ORACLE，DB2中的row_number()类似。性能比用TOP有所提升。

在利用ROW_NUMBER分页时，总页数/行数的计算，可以有这几种写法。
(1) 单独的SQL语句去获得总行数

select COUNT(*) AS TotRows
from split_pages
GO
declare @page_no int
declare @page_size int

set @page_no = 2
set @page_size = 10

;with tmp
AS
(
select *,
ROW_NUMBER() OVER(order by ID) num
from split_pages
)
select ID, Name
from tmp
where num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_no
order by num

分页开始与结束：

a.起始页从1开始

int startNo = (pageIndex - 1) * pageSize + 1;
int endNo = pageIndex * pageSize;

b.起始页从0开始

int startNo = pageIndex * pageSize + 1;
int endNo = pageIndex * pageSize + pageSize;

(2) 在ROW_NUMBER的同时用COUNT计算总行数

declare @page_no int
declare @page_size int

set @page_no = 2
set @page_size = 10

;WITH tmp
AS
(
select *, 
ROW_NUMBER() OVER(order by ID) num, 
COUNT(*) OVER() total
from split_pages
)
select ID, Name
from tmp
where num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_no
order by num

(3) 仅使用ROW_NUMBER计算总行数，IO最少

declare @page_no int
declare @page_size int

set @page_no = 2
set @page_size = 10

;with tmp
as
(
select *, 
ROW_NUMBER() OVER(order by ID) num, 
ROW_NUMBER() OVER(order by ID desc) num_desc
from split_pages
)
select ID, Name, num_desc + num -1 as total
from tmp
where num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_no
order by num

三. OFFSET/FETCH NEXT
SQL Server 2012的新语法，类似MYSQL，POSTGRESQL中的LIIMIT/OFFSET，据称性能比ROW_NUMBER又有了提升。

declare @page_no int
declare @page_size int

set @page_no = 3
set @page_size = 10

SELECT *,COUNT(*) OVER() AS Total
FROM split_pages
ORDER BY ID
OFFSET (@page -1) * @size ROWS
FETCH NEXT @size ROWS ONLY;

拼接分页查询：

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageIndex">分页索引,起始页从1开始</param>
        /// <param name="pageSize"></param>
        /// <param name="orderBy">排序字段(必传)，多字段排序：DeliveryDateTime DESC,SendOrder,SerialNumber</param>
        /// <param name="sortOrder">DESC 或 ASC;多字段可将其它字段的排序标识放在orderby，此处放默认排序</param>
        /// <returns></returns>
        public static string ToPagedSqlStr(string sql, int pageIndex = 1, int pageSize = 1000, string orderBy = "id", string sortOrder="ASC")
        {
            if (pageIndex < 1)
            {
                throw new ArgumentOutOfRangeException(nameof(pageIndex));
            }
            if (pageSize <= 0)
            {
                throw new ArgumentOutOfRangeException(nameof(pageSize));
            }
            int startNo = (pageIndex - 1) * pageSize + 1;
            int endNo = pageIndex * pageSize;
            var pageDataSql =
                $"SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {orderBy} {sortOrder}) AS tempid,* FROM ({sql}) AS DataSource) AS b WHERE b.tempid BETWEEN {startNo} AND {endNo}";
            return pageDataSql;
        }

        public static string ToCountSqlStr(string sql)
        {
            return $"select count(1) from ({sql}) t";
        }

分页拿取结果
List<Maticsoft.Model.Shop.Order.OrderInfo> orders = new List<Maticsoft.Model.Shop.Order.OrderInfo>();
int pageSize = 800;
int n = orderCodes.Count / pageSize;
for (int i = 1; i <= n + 1; i++)
{
var pageOrderCodes = orderCodes.Skip((i - 1) * pageSize).Take(pageSize).ToList();
var queryorders = orderBll.ShipedOrderByCodes(pageOrderCodes);
orders.AddRange(queryorders);
}