DB2 的 LIMIT 等效项 [英] Equivalent of LIMIT for DB2

问题描述

您如何在 DB2 for iSeries 中执行 LIMIT?

How do you do LIMIT in DB2 for iSeries?

我有一个超过 50,000 条记录的表，我想返回 0 到 10,000 条记录，以及 10,000 到 20,000 条记录.

I have a table with more than 50,000 records and I want to return records 0 to 10,000, and records 10,000 to 20,000.

我知道在 SQL 中，您在查询末尾写 LIMIT 0,10000 表示 0 到 10,000，LIMIT 10000,10000 在查询结束时表示 10000到 20,000

I know in SQL you write LIMIT 0,10000 at the end of the query for 0 to 10,000 and LIMIT 10000,10000 at the end of the query for 10000 to 20,000

那么，这在 DB2 中是如何完成的呢?代码和语法是什么?(感谢完整的查询示例)

So, how is this done in DB2? Whats the code and syntax? (full query example is appreciated)

推荐答案

开发了这个方法:

您需要一个可以排序的具有唯一值的表.

You NEED a table that has an unique value that can be ordered.

如果您想要 10,000 到 25,000 行并且您的表格有 40,000 行，首先您需要获取起点和总行数:

If you want rows 10,000 to 25,000 and your Table has 40,000 rows, first you need to get the starting point and total rows:

int start = 40000 - 10000;

int 总计 = 25000 - 10000;

然后通过代码将这些传递给查询:

And then pass these by code to the query:

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

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