MSSQL 2012 & 2008 分頁與計數 {id="MSSQL-2012-And-2008-Pagination-And-Count"}

SELECT col1, col2, ... FROM ... WHERE ... ORDER BY -- this is a MUST there must be ORDER BY statement -- the paging comes here OFFSET 10 ROWS -- skip 10 rows FETCH NEXT 10 ROWS ONLY; -- take 10 rows

SELECT ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber,* FROM Users WHERE (@IsDeleted IS NULL OR IsDeleted=@IsDeleted) ORDER BY Id OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;

SELECT * FROM( SELECT *, --先用ROW_NUMBER()替每筆資料設定編號 ROW_NUMBER() OVER(ORDER BY EmployeeId DESC) AS rowNumber, --再用COUNT()依照欄位分別計數 COUNT(*) OVER(PARTITION BY DepartmentId) AS Total FROM EmployeeAndDepartmentData WHERE DepartmentId = 1) TempTable --最後找出編號介於5-10的資料 WHERE rowNumber BETWEEN 5 AND 10