本文严禁在未征得本人同意的情况下以任何形式进行转载。本人只接受在邮件中的转载申请,如需转载,请发送邮件至 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 …