[私人小抄] SQL server 分頁

 SQL server 分頁  小抄

SQL 2008

SELECT    data.ID,
          data.Caption,
          data.CreateTime
FROM 
(
    SELECT  ROW_NUMBER() OVER(ORDER BY BN.CreateTime DESC) AS 'RowNumber', 
            Demo.ID
            Demo.Caption,
            Demo.CreateTime
    FROM    Demo
    WHERE   ID = @ID                                                
) AS data
WHERE data.RowNumber BETWEEN @Start AND @End

SQL 2012 之後

SELECT   ID, Caption, CreateTime
FROM     dbo.Demo
ORDER BY CreateTime DESC
OFFSET @Start ROWS
FETCH NEXT @PageCount ROWS ONLY;


算頁數

TotalPage = (int)Math.Ceiling((double)totalCount / (double)pageCount);

 

補充 MySQL SQLLite

SELECT * FROM dbo.Demo limit @Start,@PageCount

如果內容有誤請多鞭策謝謝