1.
SELECT TOP 页大小 * FROM table WHERE 主键 NOT IN ( SELECT TOP (页码-1)*页大小 主键 FROM table WHERE 查询条件 ORDER BY 排序条件 ) ORDER BY 排序条件
SELECT TOP 10* FROM HumanResources.Employee WHERE BusinessEntityID NOT IN (SELECT top 10 BusinessEntityID FROM HumanResources.Employee)
按着上面的模板写个示例,
发现,子查询中会返回10行数据,而主查询会返回所以得数据。俩个数据再相互比较,返回符合条件的数据。
在大数据量的时候,计算量会很大。
2.
--顺序写法: SELECT TOP 页大小 * FROM table1 WHERE id >= ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id ) A ) ORDER BY id --降序写法: SELECT TOP 页大小 * FROM table1 WHERE id <= ( SELECT ISNULL(MIN(id),0) FROM ( SELECT TOP 页大小*(页数-1)+1 id FROM table1 ORDER BY id Desc ) A ) ORDER BY id Desc
SELECT TOP 10 * FROM HumanResources.Employee WHERE BusinessEntityID>=(SELECT isnull(MAX(BusinessEntityID),0) from (SELECT top 11 BusinessEntityID from HumanResources.Employee ORDER BY BusinessEntityID) A) ORDER BY BusinessEntityID
子查询返回本页的第一个Id.
主查询直接根据条件返回记录。操作的数据减少了很多。即使是大数据量也不存在效率问题。
但是如何主键不能按照某种顺序进行排序,这个方法就不怎么好用了。
3.但是可以使用row_number函数为不能排序的列建立一个行号。
SELECT TOP 页大小 *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1) A
WHERE RowNumber > 页大小*(页数-1)
SELECT TOP 10 * from (SELECT ROW_NUMBER()OVER (ORDER BY BusinessEntityID) as RowNumber,* from HumanResources.Employee)A where RowNumber> 10
根据第一中方法写的存储过程,测试一下是否好用:
CREATE proc GetNextPage
@rows int,
@pageIndex int
as
begin
select TOP (@rows) * from HumanResources.Employee
where BusinessEntityID
not in
(
SELECT TOP (@rows*@pageIndex) BusinessEntityID from HumanResources.Employee
)
end
exec getnextPage 10, 1