如何选择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 Server


• MySQL


• PostgreSQL


• SQLite


• Oracle

• SQL Server
• MySQL
• PostgreSQL
• SQLite
• Oracle

我目前在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的网络日志

更新：请参阅 Troels Arvin的回答关于SQL标准。

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.

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