DataGrid连接Access的快速分页法（3）——SQL语句的选用（降序）

DataGrid连接Access的快速分页法（3）——SQL语句的选用（降序）

三、降序

（1）@PageIndex <= @FirstIndex

SELECT TOP @PageSize @QueryFields

FROM @TableName

WHERE @Condition

ORDER BY @PrimaryKey DESC

（2）@FirstIndex < @PageIndex <= @MiddleIndex

SELECT TOP @PageSize @QueryFields

FROM @TableName

WHERE @PrimaryKey < (

SELECT MIN(@PrimaryKey) FROM (

SELECT TOP @PageSize*@PageIndex @PrimaryKey

FROM @TableName

WHERE @Condition

ORDER BY @PrimaryKey DESC

) TableA

) WHERE @Condition

ORDER BY @PrimaryKey DESC

（3）@MiddleIndex < @PageIndex < @LastIndex

SELECT * FROM (

SELECT TOP @PageSize @QueryFields

FROM @TableName

WHERE @PrimaryKey > (

SELECT MAX(@PrimaryKey) FROM (

SELECT TOP (@RecordCount-@PageSize*(@PageIndex+1)) @PrimaryKey

FROM @TableName

WHERE @Condition

-- ORDER BY @PrimaryKey ASC

) TableA

) WHERE @Condition

-- ORDER BY @PrimaryKey ASC

) TableB

ORDER BY @PrimaryKey DESC

（4）@PageIndex >= @LastIndex

SELECT * FROM (

SELECT TOP (@RecordCount-@PageSize*@LastIndex) @QueryFields

FROM @TableName

WHERE @Condition

ORDER BY @PrimaryKey ASC

) TableA

ORDER BY @PrimaryKey DESC

四、总结

通过上面的讨论，相信大家应该看到了该分页方法的优势所在。在下一篇中，我将给大家一个动态生成上面 SQL 语句的类。

作者：黎波