有100个产品,其中最高价格的为200元,而200元的产品共有40个,
现在好了,对每页30进行分页:
declare @PageSize int--30
declare @Page int
declare @LowerCount int
if @Page = 1
begin
select top(@PageSize) from dbo.Product order by Price desc
end
else
begin
set @LowerCount = (@Page-1)*@PageSize
select * from dbo.Product
where Price < (select min(Price) from (select top (@LowerCount) Price from dbo.Product order by Price desc AS tblTmp ))
order by Price desc
end
现在好了,第1页是正常的,第二页,则丢失了10个200元的产品了!
所以,需要作如下改进:
if @Page = 1
begin
select top(@PageSize) from dbo.Product order by Price desc
end
else
begin
set @LowerCount = (@Page-1)*@PageSize
select * from dbo.Product
where Id not in (select top (@LowerCount) Id from dbo.Product order by Price desc,Id desc)
order by Price desc,Id desc
end
注:dbo.Product.Id为标识列(1,1)
小结:
第一个解法效率最高,但不适用于不具有唯一性的列,第二个解法效率低一点,但通用性最好。