在 DB2 中获取行 [英] Fetching rows in DB2

问题描述

我知道在 DB2(使用 9.7 版)中，我可以使用以下查询选择表的前 10 行:

选择 *从我的表按编号订购仅获取前 10 行

但是，例如，我怎样才能获得第 11 到 20 行?我无法使用主键或 ID 来帮助我...

提前致谢！

解决方案

这是一个示例查询，它将从表中获取包含州名称、缩写等的行.

选择 *从 (SELECT stabr, stname, ROW_NUMBER() OVER(ORDER BY stname) AS rownumber从状态WHERE stcnab = '美国') 作为 xxxWHERE rownumber BETWEEN 11 AND 20 ORDER BY stname

<块引用>

ORDER BY 是保证行编号一致所必需的在查询执行之间.

I know in DB2 (using version 9.7) I can select the first 10 rows of a table by using this query:

SELECT * 
FROM myTable
ORDER BY id
FETCH FIRST 10 ROWS ONLY

But how can I get, for example, rows 11 to 20? I can't use the primary key or the ID to help me...

Thanks in advance!

解决方案

Here's a sample query that will get rows from a table contain state names, abbreviations, etc.

SELECT *
FROM (
   SELECT stabr, stname, ROW_NUMBER() OVER(ORDER BY stname) AS rownumber
   FROM states
   WHERE stcnab = 'US'
) AS xxx
WHERE rownumber BETWEEN 11 AND 20 ORDER BY stname

Edit: ORDER BY is necessary to guarantee that the row numbering is consistent between executions of the query.

这篇关于在 DB2 中获取行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！