根据业务需求把数据拿出来放到一个临时表里面,然后拿总数及分页数据,最后删掉临时表。
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