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

2021-11-20 00:00:00 postgresql sql database mysql oracle

我有兴趣学习一些(理想情况下)数据库不可知的从数据库表中选择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 Server
  • 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's answer 关于 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.

相关文章