主流数据库之间对SQL:2003标准的不同实现方法比较(第四部分 查询结果集中间n行数据)

本文严禁在未征得本人同意的情况下以任何形式进行转载。本人只接受在邮件中的转载申请，如需转载，请发送邮件至 betteryou@126.com。  

 

带有偏移量的限制

 

目标：仅需要结果集中的n行数据，并试图忽略前面m行的数据。通常只在有ORDER BY的表达式中才有意义。

 

标准(Standard)	ID为T611的非核心特性指定了window函数，其中一个为ROW_NUMBER() OVER：   SELECT * FROM ( SELECT     ROW_NUMBER() OVER (ORDER BY key ASC) AS rownum,     Columns FROM tablename ) AS foo WHERE rownum > skip AND rownum <= (n+skip)   另外，如果编程环境支持，也可以使用游标(ID为E121的核心特性)： DECLARE cursor-name CURSOR FOR … OPEN cursor-name FETCH RELATIVE number-of-rows-to-skip CLOSE cursor-name
PostgreSQL	不支持ROW_NUMBER()。支持游标。   ROW_NUMBER()的替代方案：   SELECT columns FROM tablename ORDER BY key ASC LIMIT n OFFSET skip
DB2	支持基于window函数的方法。   关于游标：DB2 for Linux/Unix/Windows不支持FETCH RELATIVE。请查看DB2在各编程环境下的接口是否支持SQLFetchScroll()。
MS SQL Server	支持标准中描述的两种方法。   MSSQL 2000不支持ROW_NUMBER()，但可使用MSSQL特定的语句：   SELECT * FROM( SELECT TOP n * FROM (     SELECT TOP z columns      -- (z=n+skip)     FROM tablename     ORDER BY key ASC ) AS FOO ORDER BY key DESC  ) AS BAR ORDER BY key ASC
MySQL	不支持标准中讲述的方法。替代解决方案：   SELECT columns FROM tablename ORDER BY key ASC LIMIT n OFFSET skip   在MySQL的较早版本中，LIMIT的语法没有那么明确：   … LIMIT [skip,] n
Oracle	支持ROW_NUMBER()。Oracle的游标不是符合标准的。   因为Oracle不支持子查询后通过AS命名，所以需要将标准SQL稍加修改。另一个需要修改的原因是Oracle将ROWNUM视为关键字：   SELECT * FROM ( SELECT     ROW_NUMBER() OVER (ORDER BY key ASC) AS rn,     Columns FROM tablename ) WHERE rn > skip AND r <= (n+skip)

 

各种其他限制和偏移量限制经常用到：例如每次查看N行数据，以免用户收到太长的完整结果集，或者使用分页来进行查看。在这种情况下，注意不要对非唯一的列进行排序。

 

来看下例(使用了PostgreSQL)：

 

SELECT * FROM person ORDER BY age ASC;

 

person_id | person_name | age

-----------+-------------+-----

         7 | Hilda       |  12

         8 | Bill        |  12

         4 | Joe         |  23

         2 | Veronica    |  23

         3 | Michael     |  27

         9 | Marianne    |  27

         1 | Ben         |  50

        10 | Michelle    |  50

         5 | Irene       |  77

         6 | Vivian      |  77

 

 

现在假设最终用户需要每次显示5行。结果集通过两个查询来分别进行排序，如果用PostgreSQL的语法就是：

 

SELECT * FROM person ORDER BY age ASC LIMIT 5;

 

person_id | person_name | age

-----------+-------------+-----

         7 | Hilda       |  12

         8 | Bill        |  12

         4 | Joe         |  23

         2 | Veronica    |  23

         3 | Michael     |  27

 

SELECT * FROM person ORDER BY age ASC LIMIT 5 OFFSET 5;

 

person_id | person_name | age

-----------+-------------+-----

         3 | Michael     |  27

         1 | Ben         |  50

        10 | Michelle    |  50

         5 | Irene       |  77

         6 | Vivian      |  77

 

注意，Marianne并未在这两个单独的结果集众显示。

 

这样的问题可以通过对结果集进行确定性排序解决，例如：

 

SELECT * FROM person ORDER BY age ASC, person_id ASC …

 

原文参考：Comparison of different SQL implementations