SQL语句学习实例：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