SQL 中的 LIMIT 语句有多通用？

我正在推广 Django DB 复制应用程序，它使用以下语句：

SELECT %s FROM %s LIMIT 1

获取 1 行并使用 Python DBAPI 来描述字段，它可以在 ORACLE 和 MySQL 中正常工作，但是 LIMIT 语句的跨平台程度如何？

LIMIT已经在各种开源数据库中变得相当流行，但不幸的是，事实是OFFSET分页是所有 SQL 功能中标准化程度最低的，直到SQL:2008 http://en.wikipedia.org/wiki/SQL:2008.

直到那时，jOOQ 用户手册页面LIMIT clause http://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/limit-clause/显示如何在每种 SQL 方言中形成各种等效语句：

-- MySQL, H2, HSQLDB, Postgres, and SQLite
SELECT * FROM BOOK LIMIT 1 OFFSET 2

-- CUBRID supports a MySQL variant of the LIMIT .. OFFSET clause
SELECT * FROM BOOK LIMIT 2, 1

-- Derby, SQL Server 2012, Oracle 12c, SQL:2008 standard
-- Some need a mandatory ORDER BY clause prior to OFFSET
SELECT * FROM BOOK [ ORDER BY ... ] OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

-- Ingres
SELECT * FROM BOOK OFFSET 2 FETCH FIRST 1 ROWS ONLY

-- Firebird
SELECT * FROM BOOK ROWS 2 TO 3

-- Sybase SQL Anywhere
SELECT TOP 1 ROWS START AT 3 * FROM BOOK

-- DB2 (without OFFSET)
SELECT * FROM BOOK FETCH FIRST 1 ROWS ONLY

-- Sybase ASE, SQL Server 2008 (without OFFSET)
SELECT TOP 1 * FROM BOOK

现在，这些都非常简单，对吧？当你必须模仿他们时，令人讨厌的部分来了：

-- DB2 (with OFFSET), SQL Server 2008 (with OFFSET), 
SELECT * FROM (
  SELECT BOOK.*, 
    ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN
  FROM BOOK
) AS X
WHERE RN > 2
AND RN <= 3

-- DB2 (with OFFSET), SQL Server 2008 (with OFFSET)
-- When the original query uses DISTINCT!
SELECT * FROM (
  SELECT DISTINCT BOOK.ID, BOOK.TITLE 
    DENSE_RANK() OVER (ORDER BY ID ASC, TITLE ASC) AS RN
  FROM BOOK
) AS X
WHERE RN > 2
AND RN <= 3

-- Oracle 11g and less
SELECT * 
FROM (
  SELECT b.*, ROWNUM RN 
  FROM (
    SELECT *
    FROM BOOK
    ORDER BY ID ASC
  ) b
  WHERE ROWNUM <= 3
) 
WHERE RN > 2

阅读有关ROW_NUMBER() vs. DENSE_RANK()理由在这里 http://blog.jooq.org/2013/10/09/sql-trick-row_number-is-to-select-what-dense_rank-is-to-select-distinct/

选择你的毒药;-)