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

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

 

带有偏移量的限制<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

目标:仅需要结果集中的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;

 


 

 

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

 

SELECT * FROM person ORDER BY age ASC LIMIT 5;

 


 

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

 


 

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

 

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

 

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

 

原文参考:Comparison of different SQL implementations

阅读更多
换一批

没有更多推荐了,返回首页