[数据库教程] 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
关键词：数据库,SQL,语句