更新时间:2023-12-01 09:38:58
在标准的可选部分中有一些方法可以做到这一点,但很多数据库都支持自己的方法.
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.