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

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

 

带有偏移量的限制

 

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

 

标准(Standard)

IDT611的非核心特性指定了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)

 

另外,如果编程环境支持,也可以使用游标(IDE121的核心特性)

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稍加修改。另一个需要修改的原因是OracleROWNUM视为关键字:

 

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

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页