如何选择 SQL 数据库表中的第 n 行? [英] How to select the nth row in a SQL database table?

问题描述

我有兴趣学习一些(理想情况下)与数据库无关的从数据库表中选择第 n 行的方法.看看如何使用以下数据库的本机功能来实现这一点也很有趣:

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:

• SQL 服务器
• MySQL
• PostgreSQL
• SQLite
• 甲骨文

我目前在 SQL Server 2005 中执行以下类似操作，但我有兴趣了解其他更不可知的方法:

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

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

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

Credit for the above SQL: Firoz Ansari's Weblog

更新:参见 Troels Arvin 的回答关于 SQL 标准.Troels，你有我们可以引用的链接吗?

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

推荐答案

在标准的可选部分有这样做的方法，但是很多数据库都支持自己的方法.

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

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

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

PostgreSQL 和 MySQL 基本上都支持非标准的:

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

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

Oracle, DB2 and MSSQL supports the standard windowing functions:

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)

更新:从 PostgreSQL 8.4 开始支持标准的窗口函数，所以希望第二个示例也适用于 PostgreSQL.

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

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

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

这篇关于如何选择 SQL 数据库表中的第 n 行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！