問題描述
我有興趣學習一些(理想情況下)數據庫不可知的從數據庫表中選擇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.
這篇關于如何選擇SQL數據庫表中的第n行?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！