数据量大的SQL分页查询优化

我们平常写分页sql，一般都会分两次查询，一次查询结果，一次查询总行数。下面这条sql做了一些优化，只需要查询一次。
代码如下：

declare @nGotoPage int,@nPageSize int;
set @nGotoPage = 1;
set @nPageSize  = 100000;
with T_Data(RowID,ID,CID,DisplayName,WriteDate,Body)
as
(
    select Row_Number() over (order by ID) as RowID, 
            ID,CID,DisplayName,WriteDate,Body
     from Test
     where CID=1 

),T_DataCount(nRecordCount)
as
(
    select count(*) from T_Data
)
select _RecordCount=t2.nRecordCount,_PageCount=Ceiling(t2.nRecordCount * 1.0 / @nPageSize),
 t1.* from T_Data as T1, T_DataCount as t2 
where  RowID > ((@nGotoPage - 1) * @nPageSize) and RowID <= (@nGotoPage * @nPageSize);

 

转载于:https://www.cnblogs.com/zar901007/p/3643598.html