SQL分页

根据业务需求把数据拿出来放到一个临时表里面，然后拿总数及分页数据，最后删掉临时表。

SELECT M.*
INTO #Temp
FROM Table1 M
WHERE M.CreatedBy = 'System' AND M.Type = 1 AND M.Status = 1
SELECT COUNT(ID) AS TotalCount FROM #Temp;
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS RN,* FROM #Temp)Temp WHERE RN > 10 * (1 - 1) AND RN <= 10 * 1;
                                                      
DROP TABLE #Temp;

利用TOP分页，用参数化查询的时候，TOP关键字后面的参数要用括号括起来

--模板
SELECT TOP (@PageSize)  * FROM Table1 WHERE ID NOT IN (SELECT TOP ((@PageSize) * ((@PageIndex) - 1)) ID FROM Table1 ORDER BY ID DESC) ORDER BY ID DESC
--实例
SELECT TOP 10  * FROM Table1 WHERE ID NOT IN (SELECT TOP (10 * (2 - 1)) ID FROM Table1 ORDER BY ID DESC) ORDER BY ID DESC