MySQL 如何选择SQL数据库表中的第n行？

How to select the nth row in a SQL database table?

提问by Charles Roper

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases:

我有兴趣学习一些（理想情况下）数据库不可知的从数据库表中选择第n行的方法。看看如何使用以下数据库的本机功能来实现这一点也很有趣：

• SQL Server
• MySQL
• PostgreSQL
• SQLite
• Oracle

• 数据库服务器
• MySQL
• PostgreSQL
• SQLite
• 甲骨文

I am currently doing something like the following in SQL Server 2005, but I'd be interested in seeing other's more agnostic approaches:

我目前正在 SQL Server 2005 中做类似以下的事情，但我有兴趣看到其他人更不可知的方法：

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber = 1000000

Credit for the above SQL: Firoz Ansari's Weblog

上述 SQL 的功劳：Firoz Ansari 的博客

Update:See Troels Arvin's answerregarding the SQL standard. Troels, have you got any links we can cite?

更新：请参阅Troels Arvin关于 SQL 标准的回答。Troels，你有任何我们可以引用的链接吗？

采纳答案by Henrik Gustafsson

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

在标准的可选部分中有一些方法可以做到这一点，但很多数据库都支持自己的方法。

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

一个非常好的讨论这个和其他事情的网站是http://troels.arvin.dk/db/rdbms/#select-limit。

Basically, PostgreSQL and MySQL supports the non-standard:

基本上，PostgreSQL 和 MySQL 支持非标准：

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

Oracle、DB2 和 MSSQL 支持标准的窗口函数：

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

（我只是从上面链接的网站复制的，因为我从未使用过这些数据库）

Update:As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

更新：从 PostgreSQL 8.4 开始，支持标准窗口函数，因此希望第二个示例也适用于 PostgreSQL。

Update:SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

更新：SQLite 在 2018 年 9 月 15 日的 3.25.0 版中添加了窗口函数支持，因此这两种形式都可以在 SQLite 中使用。

回答by Neall

PostgreSQL supports windowing functionsas defined by the SQL standard, but they're awkward, so most people use (the non-standard) LIMIT/ OFFSET:

PostgreSQL 支持SQL 标准定义的窗口函数，但它们很笨拙，所以大多数人使用（非标准）LIMIT/OFFSET：

SELECT
    *
FROM
    mytable
ORDER BY
    somefield
LIMIT 1 OFFSET 20;

This example selects the 21st row. OFFSET 20is telling Postgres to skip the first 20 records. If you don't specify an ORDER BYclause, there's no guarantee which record you will get back, which is rarely useful.

本示例选择第 21 行。OFFSET 20告诉 Postgres 跳过前 20 条记录。如果您不指定ORDER BY子句，则无法保证您会返回哪个记录，这很少有用。

回答by Ellen Teapot

I'm not sure about any of the rest, but I know SQLite and MySQL don't have any "default" row ordering. In those two dialects, at least, the following snippet grabs the 15th entry from the_table, sorting by the date/time it was added:

我不确定其余的，但我知道 SQLite 和 MySQL 没有任何“默认”行排序。至少在这两种方言中，以下代码段从 the_table 中获取第 15 个条目，按添加日期/时间排序：

SELECT * FROM the_table ORDER BY added DESC LIMIT 1,15

(of course, you'd need to have an added DATETIME field, and set it to the date/time that entry was added...)

（当然，您需要添加一个 DATETIME 字段，并将其设置为添加条目的日期/时间...）

回答by Ben Breen

SQL 2005 and above has this feature built-in. Use the ROW_NUMBER() function. It is excellent for web-pages with a << Prev and Next >> style browsing:

SQL 2005 及更高版本内置了此功能。使用 ROW_NUMBER() 函数。它非常适合具有 << Prev and Next >> 样式浏览的网页：

Syntax:

句法：

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
            *
        FROM
            Table_1
    ) sub
WHERE
    RowNum = 23

回答by Tim Saunders

I suspect this is wildly inefficient but is quite a simple approach, which worked on a small dataset that I tried it on.

我怀疑这是非常低效的，但这是一种非常简单的方法，它适用于我尝试过的一个小数据集。

select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc

This would get the 5th item, change the second top number to get a different nth item

这将获得第 5 个项目，更改第二个顶部数字以获得不同的第 n 个项目

SQL server only (I think) but should work on older versions that do not support ROW_NUMBER().

仅 SQL 服务器（我认为）但应该适用于不支持 ROW_NUMBER() 的旧版本。

回答by Nick Berardi

1 small change: n-1 instead of n.

1个小变化：n-1而不是n。

select *
from thetable
limit n-1, 1

回答by Rameshwar Pawale

Verify it on SQL Server:

在 SQL Server 上验证它：

Select top 10 * From emp 
EXCEPT
Select top 9 * From emp

This will give you 10th ROW of emp table!

这将为您提供 emp 表的第 10 行！

回答by Troels Arvin

Contrary to what some of the answers claim, the SQL standard is not silent regarding this subject.

与某些答案所声称的相反，SQL 标准并没有就此主题保持沉默。

Since SQL:2003, you have been able to use "window functions" to skip rows and limit result sets.

从 SQL:2003 开始​​，您就可以使用“窗口函数”来跳过行并限制结果集。

And in SQL:2008, a slightly simpler approach had been added, using
OFFSET skipROWS FETCH FIRST nROWS ONLY

在 SQL:2008 中，添加了一个稍微简单的方法，使用
OFFSET skipROWS FETCH FIRST nROWS ONLY

Personally, I don't think that SQL:2008's addition was really needed, so if I were ISO, I would have kept it out of an already rather large standard.

就我个人而言，我不认为 SQL:2008 的添加是真正需要的，所以如果我是 ISO，我会把它排除在一个已经相当大的标准之外。

回答by Adam V

When we used to work in MSSQL 2000, we did what we called the "triple-flip":

当我们过去在 MSSQL 2000 中工作时，我们做了我们所说的“三重翻转”：

EDITED

已编辑

DECLARE @InnerPageSize int
DECLARE @OuterPageSize int
DECLARE @Count int

SELECT @Count = COUNT(<column>) FROM <TABLE>
SET @InnerPageSize = @PageNum * @PageSize
SET @OuterPageSize = @Count - ((@PageNum - 1) * @PageSize)

IF (@OuterPageSize < 0)
    SET @OuterPageSize = 0
ELSE IF (@OuterPageSize > @PageSize)
    SET @OuterPageSize = @PageSize

DECLARE @sql NVARCHAR(8000)

SET @sql = 'SELECT * FROM
(
    SELECT TOP ' + CAST(@OuterPageSize AS nvarchar(5)) + ' * FROM
    (
        SELECT TOP ' + CAST(@InnerPageSize AS nvarchar(5)) + ' * FROM <TABLE> ORDER BY <column> ASC
    ) AS t1 ORDER BY <column> DESC
) AS t2 ORDER BY <column> ASC'

PRINT @sql
EXECUTE sp_executesql @sql

It wasn't elegant, and it wasn't fast, but it worked.

它不优雅，速度也不快，但确实有效。

回答by Aditya

SQL SERVER

SQL服务器

---

---

Select n' th record from top

从顶部选择第 n 条记录

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n

select n' th record from bottom

从底部选择第 n 条记录

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n