SQLServer 两种分页方式

方法一：百万级数据分页

--获取6000000~600010条数据
DECLARE @pageindex INT=6000;
DECLARE @pagesize INT=10;
DECLARE @top INT=@pageindex*@pagesize+1;
PRINT @top;
DECLARE @preid VARCHAR(36);
DECLARE @prefield DATETIME;
SELECT TOP (@top) @preid=ID,@prefield=TallyDate FROM TABLE ORDER BY TallyDate DESC,ID
PRINT @preid PRINT @prefield
SELECT TOP (@pagesize) * FROM TABLE WHERE TallyDate=@prefield AND ID>=@preid OR TallyDate<@prefield ORDER BY TallyDate DESC,ID 

方法二：十万级数据分页

--获取6000000~600010条数据
SELECT B.ROWNO,
       A.*
FROM TABLE A,
(
    SELECT TOP 600010
           ROW_NUMBER() OVER (ORDER BY DataTime DESC,ID) AS ROWNO,
           ID
    FROM TABLE
) B
WHERE A.ID = B.ID
      AND B.ROWNO > 600000
ORDER BY B.ROWNO;