SQL Server中的分页 [英] Pagination in SQL Server

问题描述

如何限制查询的结果(以我的情况为6万行)，并仅从X行到Y行中进行选择?

How do i limit the result of a query (in my case about 60K rows) and select only from the X row to the Y row?

如果我使用ROW_NUMBER()，我将不喜欢我的查询，因为它涉及2个选择查询..一个返回行，另一个选择需要的部分

If I use ROW_NUMBER() I don't like my query because it involves 2 select queries .. one to return the rows and one to select the portion I need

更新:

这是我现在使用的查询:

Here's the query I use now:

SELECT  *
FROM    (
        SELECT  row_number() OVER (ORDER BY E.LastChangeDate DESC) AS row, E.*, U.[DisplayName] AS EntryCreatorDisplayName, U.[Email] AS EntryCreatorEmail
        FROM    entries e
        INNER JOIN
                users u
        ON      e.fk_user= u.id
        WHERE   e.EntryRank = 2
                AND u.Administrator = 1
        ) as TableWithRows
WHERE   (row >= 31 AND row <= 60)

推荐答案

WITH    q AS
        (
        SELECT  TOP (@Y) m.*, ROW_NUMBER() OVER (ORDER BY mycol) AS rn
        FROM    mytable m
        ORDER BY
                mycol
        )
SELECT  *
FROM    q
WHERE   rn >= @X

在SQL Server 2000中:

SELECT  *
FROM    (
        SELECT  TOP (@Y - @X) *
        FROM    (
                SELECT  TOP (@X) *
                FROM    mytable
                ORDER BY
                        mycol
                ) q
        ORDER BY
                mycol DESC
        ) q2
ORDER BY
        mycol

这篇关于SQL Server中的分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！