DB2的LIMIT等效方法是什么？

Question

如何在DB2 for iSeries中使用LIMIT？

我有一个包含超过50,000条记录的表，我想返回0到10,000条记录和10,000到20,000条记录。

我知道在SQL中，你可以在查询末尾写上 LIMIT 0,10000 来返回0到10,000条记录，以及在查询末尾写上 LIMIT 10000,10000 来返回10,000到20,000条记录。

那么，在DB2中该如何实现呢？代码和语法是什么？ （最好提供完整的查询示例）

- elcool

---

ROW_NUMBER() 函数仅在 iSeries DB2 V5R4 中实现。对于早期版本，请尝试使用类似的 RRN() 函数。 - Paul Morgan

RRN()与row_number()完全不同。 - Brandon Peterson

没有对我起作用。语法错误。 - elcool

尝试使用RRN（文件名），它将提供行的物理相对记录号。如果已删除行，则RRN不会连续并且可能会跳过数字。如果没有删除发生，则基于添加，RRN也不会按键顺序连续。无论如何，RRN将是一行的唯一标识，并可用于选择表的子集。 - Paul Morgan

根据 http://programmingzen.com/2010/06/02/enabling-limit-and-offset-in-db2-9-7-2/ ，DB2从9.7.2版本开始支持限制关键字。 - lakshman

@lakshman 这是针对 DB2 for i 的问题。 - jmarkmurphy

Answer 1

使用FETCH FIRST [n] ROWS ONLY：

点击此链接

SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY
  FROM EMP
  ORDER BY SALARY DESC
  FETCH FIRST 20 ROWS ONLY;

要获取范围，您需要使用ROW_NUMBER()（从v5r4开始），并在WHERE子句中使用它：（摘自此处：http://www.justskins.com/forums/db2-select-how-to-123209.html）

SELECT code, name, address
FROM ( 
  SELECT row_number() OVER ( ORDER BY code ) AS rid, code, name, address
  FROM contacts
  WHERE name LIKE '%Bob%' 
  ) AS t
WHERE t.rid BETWEEN 20 AND 25;

- Joe

---

是的，我也发现了，呵呵。我同时在编辑问题，表明我也想要中间行。 - elcool

你需要使用 ROW_NUMBER 像这样操作：http://www.justskins.com/forums/db2-select-how-to-123209.html - Joe

ROW_NUMBER 不是一个有效的关键字。但感谢提供的链接，它给了我一个想法并且它可行。 - elcool

Answer 2

我开发了以下方法:

你需要一个具有可排序的唯一值的表格。

如果你想要获取第10000行到第25000行，而你的表格共有40000行，首先需要获取起始点和总行数:

int start = 40000 - 10000;

int total = 25000 - 10000;

然后将这些代码传递给查询：

SELECT * FROM 
(SELECT * FROM schema.mytable 
ORDER BY userId DESC fetch first {start} rows only ) AS mini 
ORDER BY mini.userId ASC fetch first {total} rows only

- elcool

---

请注意，第10000行在结果集中被排除，第一行是第10001行。 - bluish

有趣的解决方案。我原本想用它来兼容H2测试数据库...但是，遗憾的是，它的速度比SELECT row_number() OVER ( ORDER BY code )方法慢了约30倍。 - manuna

Answer 3

最近在DB2 for i 7.1和7.2中新增了OFFSET和LIMIT的支持。您需要以下DB PTF组级别才能获得此支持：

• IBM i 7.2的SF99702级别9
• IBM i 7.1的SF99701级别38

有关更多信息，请参见：文档、Wiki 中的 DB2 for i Enhancement。

- Kevin Adler

Answer 4

这是我想出的解决方案：

select FIELD from TABLE where FIELD > LASTVAL order by FIELD fetch first N rows only;

通过将LASTVAL初始化为0（或文本字段的''），然后将其设置为最近一组记录中的最后一个值，这将以N个记录的块形式遍历表。

- Tom Barron

---

我最初认为您正在设置表中的值，这在并发系统上将是非常棘手的问题。是的，在您对表进行顺序读取的情况下，这应该可以工作，尽管在列中存在相同值的数量大于“N”的情况下，您需要某种决胜者列（尽管在使用“ROW_NUMBER()”时也是如此）。必须谨慎选择初始值-如果列包含负值，则“0”显然会有问题。空值需要小心处理。如果跳过页面，则无法正常工作。 - Clockwork-Muse

谢谢您的评论。我认为有一个隐含的假设，即我们用来控制查询的字段是唯一且单调递增的。如果这些假设不成立，那么这种方法就无法访问表中的所有记录。当然，您正确地指出，您必须从一个有意义的LASTVAL开始。通常情况下，我认为您应该从“select MINIMUM(FIELD) from TABLE”返回的内容开始。如果该字段已经建立索引，大多数数据库引擎将比顺序读取整个表更有效率。 - Tom Barron

Answer 5

试试这个

SELECT * FROM
    (
        SELECT T.*, ROW_NUMBER() OVER() R FROM TABLE T
    )
    WHERE R BETWEEN 10000 AND 20000

- Lucio Menci

Answer 6

@elcool的解决方案是一个聪明的想法，但你需要知道总行数（在执行查询时甚至可能会发生变化！）。因此，我提出了一个修改版，不幸的是需要使用3个子查询而不是2个：

select * from (
    select * from (
        select * from MYLIB.MYTABLE
        order by MYID asc 
        fetch first {last} rows only 
        ) I 
    order by MYID desc
    fetch first {length} rows only
    ) II
order by MYID asc

其中{last}应该替换为我需要的最后一条记录的行号，{length}应该替换为我需要的行数，计算公式为最后一行 - 第一行 + 1。

例如，如果我想要从第10行到第25行（共16行），{last}将是25，{length}将是25-10+1=16。

- bluish

---

我鄙视那些在别人耐心回答问题时却进行踩票的人。 - user153923

Answer 7

LIMIT子句允许您限制查询返回的行数。 LIMIT子句是SELECT语句的扩展，具有以下语法：

SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT n [OFFSET m];

在这个语法中：

• n 是要返回的行数。
• m 是在返回 n 行之前要跳过的行数。

LIMIT 子句的另一个更短的版本如下：

LIMIT m, n;

这个语法表示跳过 m 行并从结果集中返回接下来的 n 行。

一个表可能以未指定的顺序存储行。如果您不使用 LIMIT 子句的 ORDER BY 子句，则返回的行也是未指定的。因此，最好始终将 LIMIT 子句与 ORDER BY 子句一起使用。

更多详细信息，请参见 Db2 LIMIT。

- Milovan Tomašević

Answer 8

您还应考虑使用OPTIMIZE FOR n ROWS子句。有关所有这些内容的更多详细信息，请参阅DB2 LUW文档中的限制SELECT语句的指南主题：

• OPTIMIZE FOR子句声明仅检索结果的子集或优先检索前几行的意图。然后，优化器可以选择访问计划，以最小化检索前几行的响应时间。

- David Sky

Answer 9

在DB2表上进行分页有两种有效的解决方案：

1-使用函数row_number()和子句OVER的技术，该技术已在另一篇文章中介绍过（“SELECT row_number() OVER ( ORDER BY ... )”）。在一些大型表上，我注意到有时会出现性能下降。

2-使用可滚动游标的技术。实现取决于所使用的语言。这种技术在大型表上似乎更加稳健。

我在明年的研讨会上介绍了PHP中实现的这两种技术。幻灯片可以在此链接上找到： http://gregphplab.com/serendipity/uploads/slides/DB2_PHP_Best_practices.pdf

抱歉，但此文档仅提供法语版本。

- gregphplab

Answer 10

这里有可用的选项：

DB2 has several strategies to cope with this problem.
You can use the "scrollable cursor" in feature.
In this case you can open a cursor and, instead of re-issuing a query you can FETCH forward and backward.
This works great if your application can hold state since it doesn't require DB2 to rerun the query every time.
You can use the ROW_NUMBER() OLAP function to number rows and then return the subset you want.
This is ANSI SQL 
You can use the ROWNUM pseudo columns which does the same as ROW_NUMBER() but is suitable if you have Oracle skills.
You can use LIMIT and OFFSET if you are more leaning to a mySQL or PostgreSQL dialect.  

- Hector