mysql - 如何在SQL数据库表中选择第n行？(How to select the nth row in a SQL database table?)

Question

I'm interested in learning some (ideally) database agnostic ways of selecting the n th row from a database table.

(我有兴趣学习一些（理想情况下）从数据库表中选择第n行的数据库不可知方法。)

It would also be interesting to see how this can be achieved using the native functionality of the following databases:

(看看如何使用以下数据库的本机功能实现此目标也将很有趣：)

• SQL Server

  (SQL服务器)

• MySQL

  (的MySQL)

• PostgreSQL

  (PostgreSQL的)

• SQLite

  (SQLite的)

• Oracle

  (甲骨文)

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的Weblog)

Update: See Troels Arvin's answer regarding the SQL standard.

(更新：有关SQL标准，请参见Troels Arvin的答案 。)

Troels, have you got any links we can cite?

(Troels，您有我们可以引用的任何链接吗？)

  ask by Charles Roper translate from so

Answer 1

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-09-15的3.25.0版本中添加了窗口功能支持，因此两种形式都可以在SQLite中使用。)