問題描述
我有興趣學(xué)習(xí)一些(理想情況下)數(shù)據(jù)庫(kù)不可知的從數(shù)據(jù)庫(kù)表中選擇n行的方法.看看如何使用以下數(shù)據(jù)庫(kù)的本機(jī)功能來實(shí)現(xiàn)這一點(diǎ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 關(guān)于 SQL 標(biāo)準(zhǔn).Troels，你有任何我們可以引用的鏈接嗎?
Update: See Troels Arvin's answer regarding the SQL standard. Troels, have you got any links we can cite?
推薦答案
在標(biāo)準(zhǔn)的可選部分中有一些方法可以做到這一點(diǎn)，但很多數(shù)據(jù)庫(kù)都支持自己的方法.
There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.
一個(gè)非常好的討論這個(gè)和其他事情的網(wǎng)站是 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 都支持非標(biāo)準(zhǔn)的:
Basically, PostgreSQL and MySQL supports the non-standard:
SELECT...
LIMIT y OFFSET x
Oracle、DB2 和 MSSQL 支持標(biāo)準(zhǔn)的窗口函數(shù):
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
(我只是從上面鏈接的站點(diǎn)復(fù)制的，因?yàn)槲覐奈词褂眠^這些數(shù)據(jù)庫(kù))
(which I just copied from the site linked above since I never use those DBs)
更新:從 PostgreSQL 8.4 開始支持標(biāo)準(zhǔn)的窗口函數(shù)，所以希望第二個(gè)示例也適用于 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 版本中添加了窗口函數(shù)支持，因此這兩種形式都可以在 SQLite 中使用.
Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.
這篇關(guān)于如何選擇SQL數(shù)據(jù)庫(kù)表中的第n行?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！