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 语句的类。
作者:黎波