是否有 ANSI SQL 替代 MYSQL LIMIT 关键字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/595123/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?
提问by Gary Willoughby
Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?
是否有 ANSI SQL 替代 MYSQL LIMIT 关键字？
The LIMIT keyword limits the number of rows returned by a SELECT e.g:
LIMIT 关键字限制 SELECT 返回的行数，例如：
SELECT * FROM People WHERE Age > 18 LIMIT 2;
returns 2 rows.
返回 2 行。
SELECT * FROM People WHERE Age > 18 LIMIT 10, 2;
returns 2 rows after the first 10.
在前 10 行之后返回 2 行。
回答by jle
this shows the different ways:
-- DB2
select * from table fetch first 10 rows only
-- Informix
select first 10 * from table
-- Microsoft SQL Server and Access
select top 10 * from table
-- MySQL and PostgreSQL
select * from table limit 10
-- Oracle
select * from (select * from table) where rownum <= 10
回答by bobince
Not in SQL:1999.
不在 SQL:1999 中。
There are two possible approaches you can use in later standards, with generally low levels of support in today's DBMSs.
您可以在以后的标准中使用两种可能的方法，但在当今的 DBMS 中通常支持程度较低。
In SQL:2008 you can use the DB/2 syntax:
在 SQL:2008 中，您可以使用 DB/2 语法：
SELECT * FROM things
ORDER BY smell
FETCH FIRST n ROWS ONLY
This only works for “LIMIT n” and not the extended “LIMIT m, n” offset syntax. In SQL:2003 you can use window functions, which can support the extended syntax but is a super PITA:
这仅适用于“LIMIT n”，而不适用于扩展的“LIMIT m, n”偏移语法。在 SQL:2003 中你可以使用窗口函数，它可以支持扩展语法但是是一个超级 PITA：
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY smell) AS rn,
FROM things
)
WHERE rn<=n -- or rn BETWEEN m+1 AND m+n
You will more usually use the DBMS-specific methods today.
今天，您将更经常地使用 DBMS 特定的方法。
回答by groovehunter
see also http://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause
另见http://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause
SELECT * FROM T LIMIT 10 OFFSET 20 -- Netezza, MySQL, PostgreSQL (also supports the standard, since version 8.4), SQLite, HSQLDB, H2
SELECT * from T WHERE ROWNUM <= 10 -- Oracle (also supports the standard, since Oracle8i)
SELECT FIRST 10 * from T -- Ingres
SELECT FIRST 10 * FROM T order by a -- Informix
SELECT SKIP 20 FIRST 10 * FROM T order by c, d -- Informix (row numbers are filtered after order by is evaluated. SKIP clause was introduced in a v10.00.xC4 fixpack)
SELECT TOP 10 * FROM T -- MS SQL Server, Sybase ASE, MS Access
SELECT TOP 10 START AT 20 * FROM T -- Sybase SQL Anywhere (also supports the standard, since version 9.0.1)
SELECT FIRST 10 SKIP 20 * FROM T -- Interbase, Firebird
SELECT * FROM T ROWS 20 TO 30 -- Firebird (since version 2.1)
SELECT * FROM T
WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY -- DB2
SELECT * FROM T
WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY -- DB2 (new rows are filtered after comparing with key column of table T)
回答by Jeremy DeGroot
I don't believe so. All the databases that I'm aware of use vendor-specific keywords for that functionality.
我不相信。我所知道的所有数据库都使用特定于供应商的关键字来实现该功能。
回答by Bill Karwin
Adding to @jle's answer:
添加到@jle 的答案：
- SQLite supports
LIMIT(MySQL/PostgreSQL) - InterBase/Firebird support
SELECT FIRSTandSKIP(like Informix)
- SQLite 支持
LIMIT(MySQL/PostgreSQL) - InterBase/Firebird 支持
SELECT FIRST和SKIP（如 Informix）
Also see Emulate MySQL LIMIT clause in Microsoft SQL Server 2000
回答by Manik Surtani
HSQL/H2 uses LIMIT like MySQL
HSQL/H2 像 MySQL 一样使用 LIMIT
回答by ジョージ
Let me link here a related SO question, with a great answer by Lukas Ederand another good answer by bobince:
让我在这里链接一个相关的 SO 问题，Lukas Eder 给出了一个很好的答案，而 bobince 给出了另一个很好的答案：
How universal is the LIMIT statement in SQL?
edit:A few more good reference links, worth to look at in similar cases:
编辑：一些更好的参考链接，在类似情况下值得一看：
