如何从T-SQL中的排序表中的第M行开始获取N行 [英] How to get N rows starting from row M from sorted table in T-SQL

问题描述

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

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

SELECT TOP 10 * FROM MyTable ORDER BY MyColumn

有没有什么有效的方法可以查询从第 N 行开始的 M 行

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

例如

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

像这样查询

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

从第 3d 行开始查询 2 行，即返回第 3d 和第 4 行.

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

推荐答案

更新 如果您使用的是 SQL 2012，则添加了新语法以使其变得非常简单.请参阅使用此查询实现分页(跳过/获取)功能

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

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

I guess the most elegant is to use the ROW_NUMBER function (available from MS 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

这篇关于如何从T-SQL中的排序表中的第M行开始获取N行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！