如何从T-SQL中有序的表中获取N行?

There is a simple way to get top N rows from any table:

有一种简单的方法可以从任何表中获取top N行:

SELECT TOP 10 * FROM MyTable ORDER BY MyColumn

Is there any efficient way to query M rows starting from row N

有什么有效的方法来查询从第N行开始的M行吗?

For example,

例如,

Id Value
1    a
2    b
3    c
4    d
5    e
6    f

And query like this

这样和查询

SELECT [3,2] * FROM MyTable ORDER BY MyColumn /* hypothetical syntax */

queries 2 rows starting from 3d row, i.e 3d and 4th rows are returned.

查询2行从3d行开始，i。e 3d和第4行返回。

17 个解决方案

#1

UPDATE If you you are using SQL 2012 new syntax was added to make this really easy. See Implement paging (skip / take) functionality with this query

如果您正在使用SQL 2012新语法，那么可以添加新语法，使之变得非常简单。使用此查询查看实现分页(跳过/获取)功能。

I guess the most elegant is to use the ROW_NUMBER function (available from MS SQL Server 2005):

我想最优雅的是使用ROW_NUMBER函数(从SQL Server 2005中可以获得):

WITH NumberedMyTable AS
(
    SELECT
        Id,
        Value,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM
        MyTable
)
SELECT
    Id,
    Value
FROM
    NumberedMyTable
WHERE
    RowNumber BETWEEN @From AND @To

#2

The problem with the suggestions in this thread and elsewhere on the web is that all the proposed solutions run in linear time with respect to the number of records. For example, consider a query like the following.

这个线程和web上其他地方的建议的问题在于，所有建议的解决方案都是在线性时间内以记录的数量运行的。例如，考虑如下的查询。

select *
from
(
    select
        Row_Number() over (order by ClusteredIndexField) as RowNumber,
        *
    from MyTable
) as PagedTable
where RowNumber between @LowestRowNumber and @HighestRowNumber;

When getting page 1, the query takes 0.577 seconds. However, when getting page 15,619, this same query takes over 2 minutes and 55 seconds.

在获取第1页时，查询需要0.577秒。然而，当获取15619页时，同样的查询需要2分55秒。

We can greatly improve this by creating a record number, index cross-table as shown in the following query. The cross-table is called PagedTable and is non-persistent.

我们可以通过创建一个记录编号、索引交叉表来极大地改进这一点，如下面的查询所示。交叉表称为PagedTable，是非持久性的。

select *
from
(
    select
        Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,
        ClusteredIndexField
    from MyTable
) as PagedTable
left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField
where RowNumber between @LowestRowNumber and @HighestRowNumber;

Like in the previous example, I tested this on a very wide table with 780,928 records. I used a page size of 50, which resulted in 15,619 pages.

和前面的例子一样，我在一个非常宽的表上测试了这个，它有780928条记录。我使用的页面大小为50，结果是15619页。

The total time taken for page 1 (the first page) is 0.413 seconds. The total time taken for page 15,619 (the last page) is 0.987 seconds, merely twice times as long as page 1. These times were measured using SQL Server Profiler and the DBMS was SQL Server 2008 R2.

第1页(第一页)的总时间为0.413秒。第15619页(最后一页)的总时间为0.987秒，仅为第1页的两倍。这些时间是使用SQL Server Profiler来度量的，而DBMS是SQL Server 2008 R2。

This solution works for any case when you are sorting your table by an index. The index does not have to be clustered or simple. In my case, the index was composed of three fields: varchar(50) asc, varchar(15) asc, numeric(19,0) asc. That the performance was excellent despite the cumbersome index just further demonstrates that this approach works.

当您按照索引对表进行排序时，该解决方案适用于任何情况。该索引不需要集群或简单。在我的案例中，索引由三个字段组成:varchar(50) asc、varchar(15) asc、numeric(19,0) asc。尽管繁琐的索引进一步说明这种方法有效，但性能还是很好。

However, it is critical that the order by clause in the Row_Number windowing function corresponds to an index. Otherwise performance will degrade to the same level as the first example.

然而，关键是在Row_Number窗口函数中的order by子句对应于一个索引。否则性能将降低到与第一个示例相同的级别。

This approach does still require a linear operation to generate the non-persistent cross-table, but since that's just an index with a row number added, it happens very quickly. In my case it took 0.347 seconds, but my case had varchars that needed to be copied. A single numeric index would take far less time.

这种方法仍然需要一个线性操作来生成非持久性的跨表，但是由于这只是一个添加了行号的索引，所以它发生得很快。在我的案例中，它花费了0.347秒，但是我的案例有varchars需要被复制。一个单一的数字索引将花费更少的时间。

For all practical purposes, this design reduces the scaling of server-side paging from a linear operation to a logarithmic operation allowing the scaling of large tables. Below is the complete solution.

出于所有实际的目的，这种设计减少了服务器端分页从线性操作到对数运算的缩放比例，允许大型表的缩放。下面是完整的解决方案。

-- For a sproc, make these your input parameters
declare
    @PageSize int = 50,
    @Page int = 15619;

-- For a sproc, make these your output parameters
declare @RecordCount int = (select count(*) from MyTable);
declare @PageCount int = ceiling(convert(float, @RecordCount) / @PageSize);
declare @Offset int = (@Page - 1) * @PageSize;
declare @LowestRowNumber int = @Offset;
declare @HighestRowNumber int = @Offset + @PageSize - 1;

select
    @RecordCount as RecordCount,
    @PageCount as PageCount,
    @Offset as Offset,
    @LowestRowNumber as LowestRowNumber,
    @HighestRowNumber as HighestRowNumber;

select *
from
(
    select
        Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,
        ClusteredIndexField
    from MyTable
) as PagedTable
left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField
where RowNumber between @LowestRowNumber and @HighestRowNumber;

#3

If you want to select 100 records from 25th record:

如果您想从第25条记录中选择100条记录:

select TOP 100 * from TableName
where PrimaryKeyField 
   NOT IN(Select TOP 24 PrimaryKeyField from TableName);

#4

In SQL 2012 you can use OFFSET and FETCH:

在SQL 2012中，可以使用偏移和获取:

SELECT *
FROM MyTable
ORDER BY MyColumn
OFFSET @N ROWS
FETCH NEXT @M ROWS ONLY;

---

I personally prefer:

DECLARE @CurrentSetNumber int = 0;
DECLARE @NumRowsInSet int = 2;

SELECT *
FROM MyTable
ORDER BY MyColumn
OFFSET @NumRowsInSet * @CurrentSetNumber ROWS
FETCH NEXT @NumRowsInSet ROWS ONLY;

SET @CurrentSetNumber = @CurrentSetNumber + 1;

where @NumRowsInSet is the number of rows you want returned and @CurrentSetNumber is the number of @NumRowsInSet to skip.

@NumRowsInSet是您想要返回的行数，@CurrentSetNumber是@NumRowsInSet跳过的数量。

#5

Ugly, hackish, but should work:

丑陋，陈腐，但应该工作:

select top(M + N - 1) * from TableName
except
select top(N - 1) * from TableName

#6

Probably good for small results, works in all versions of TSQL:

可能对小的结果有好处，适用于所有版本的TSQL:

SELECT 
        * 
FROM
     (SELECT TOP (N) * 
      FROM 
            (SELECT TOP (M + N - 1) 
             FROM 
                   Table
             ORDER BY 
                      MyColumn) qasc
      ORDER BY 
               MyColumn DESC) qdesc
 ORDER BY 
         MyColumn

#7

        -- *some* implementations may support this syntax (mysql?)
SELECT Id,Value
FROM xxx
ORDER BY Id
LIMIT 2 , 0
   ;

        -- Separate LIMIT, OFFSET
SELECT Id,Value
FROM xxx
ORDER BY Id
LIMIT 2 OFFSET 2
   ;

        -- SQL-2008 syntax
SELECT Id,Value
FROM xxx
ORDER BY Id
OFFSET 4
FETCH NEXT 2 ROWS ONLY
  ;

#8

@start = 3
@records = 2

Select ID, Value 
From
(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNum, ID,Value 
From MyTable) as sub
Where sub.RowNum between @start and @start+@records

This is one way. there are a lot of others if you google SQL Paging.

这是方法之一。如果使用谷歌SQL分页，则会有很多其他的内容。

#9

This thread is quite old, but currently you can do this: much cleaner imho

这条线很旧，但目前你可以这样做:更清洁的imho。

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

source: http://blog.sqlauthority.com/2013/12/30/sql-server-mysql-limit-and-offset-skip-and-return-only-next-few-rows-paging-solution/

来源:http://blog.sqlauthority.com/2013/12/30/sql-server-mysql-limit-and-offset-skip-and-return-only-next-few-rows-paging-solution/

#10

Following is the simple query will list N rows from M+1th row of the table. Replace M and N with your preferred numbers.

下面是简单的查询，将从表的M+1行中列出N行。用你喜欢的数字替换M和N。

Select Top N B.PrimaryKeyColumn from 
    (SELECT 
        top M PrimaryKeyColumn
     FROM 
        MyTable
) A right outer join MyTable B 
on 
    A.PrimaryKeyColumn = B.PrimaryKeyColumn
where 
    A.PrimaryKeyColumn IS NULL

Please let me know whether this is usefull for your situation.

请让我知道这对你的情况是否有用。

#11

And this is how you can achieve same goal on tables without primary key:

这就是如何在没有主键的情况下实现相同的目标:

select * from
(
    select row_number() over(order by (select 0)) rowNum,*
    from your_table
) tmp
where tmp.rowNum between 20 and 30 -- any numbers you need

#12

I read all of the responses here and finally came up with a usable solution that is simple. The performance issues arise from the BETWEEN statement, not the generation of the row numbers themselves. So I used an algorithm to do dynamic paging by passing the page number and the number of records.

我在这里阅读了所有的回复，最终找到了一个简单的可用的解决方案。性能问题是由语句之间产生的，而不是行号本身的生成。因此，我使用了一个算法，通过传递页码和记录的数量来进行动态分页。

The passes are not start row and number of rows, but rather "rows per page (500)" and "page number (4)" which would be rows 1501 - 2000. These values can be replaced by stored procedure variables so you are not locked into using a specific paging amount.

通过的不是起始行和行数，而是“每页(500)行”和“页码(4)”，这将是行1501 - 2000。这些值可以被存储过程变量替换，这样您就不会被锁定在使用特定的分页数量。

select * from (
    select
        (((ROW_NUMBER() OVER(ORDER BY MyField) - 1) / 500) + 1) AS PageNum
        , *
    from MyTable
) as PagedTable
where PageNum = 4;

#13

In order to do this in SQL Server, you must order the query by a column, so you can specify the rows you want.

为了在SQL Server中执行这个操作，您必须通过一个列命令查询，这样您就可以指定您想要的行。

You can't use the "TOP" keyword when doing this, you must use offset N rows fetch next M rows.

在执行此操作时，不能使用“TOP”关键字，必须使用偏移的N行来获取下一个M行。

Example:

例子:

select * from table order by [some_column] 
offset 10 rows
FETCH NEXT 10 rows only

You can learn more here: https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx

你可以在这里学到更多的东西:https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%。

#14

Find id for row N Then get the top M rows that have an id greater than or equal to that

找到行N的id，然后得到最大的M行，它的id大于或等于那个。

declare @N as int
set @N = 2
declare @M as int
set @M = 3

declare @Nid as int

set @Nid = max(id)
from
  (select top @N *
from MyTable
order by id)

select top @M *
from MyTable
where id >= @Nid
order by id

Something like that ... but I've made some assumptions here (e.g. you want to order by id)

这样的……但是我在这里做了一些假设(比如你想按id排序)

#15

There is a pretty straight-forward method for T-SQL, although I'm not sure if it is prestanda-effective if you're skipping a large number of rows.

对于T-SQL，有一个非常简单的方法，尽管我不确定如果您跳过了大量的行，它是否具有prestanda效果。

SELECT TOP numberYouWantToTake 
    [yourColumns...] 
FROM yourTable 
WHERE yourIDColumn NOT IN (
    SELECT TOP numberYouWantToSkip 
        yourIDColumn 
    FROM yourTable 
    ORDER BY yourOrderColumn
)
ORDER BY yourOrderColumn

If you're using .Net, you can use the following on for example an IEnumerable with your data results:

如果你使用。net，你可以使用下面的方法，例如，用你的数据结果进行IEnumerable:

IEnumerable<yourDataType> yourSelectedData = yourDataInAnIEnumerable.Skip(nubmerYouWantToSkip).Take(numberYouWantToTake);

This has the backside that you're getting all the data from the data storage.

这是你从数据存储中获取所有数据的背面。

#16

Why not do two queries:

为什么不做两个查询:

select top(M+N-1) * from table into temp tmp_final with no log;
select top(N-1) * from tmp_final order by id desc;

#17

SELECT * FROM (
  SELECT
    Row_Number() Over (Order by (Select 1)) as RawKey,
    * 
  FROM [Alzh].[dbo].[DM_THD_TRANS_FY14]
) AS foo
WHERE RawKey between 17210400 and 17210500