代码之家 › 专栏 › 技术社区 › elcool codeVerine

相当于DB2的限制

ibm-midrange limit db2

elcool codeVerine · 技术社区 · 15 年前

你好吗 LIMIT 在DB2 for iSeries中?

我有一个超过50000条记录的表,我想返回0到10000条记录,以及10000到20000条记录。

我知道你写的SQL LIMIT 0,10000 在查询0到10000和 LIMIT 10000,10000 在查询10000到20000的末尾

那么,这是如何在DB2中完成的呢?代码和语法是什么? (请提供完整的查询示例)

9 回复 | 直到 8 年前

132

bluish dmajkic 13 年前

FETCH FIRST [n] ROWS ONLY

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.perf/db2z_fetchfirstnrows.htm

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

ROW_NUMBER() 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;

elcool codeVerine 15 年前

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

Kevin Adler 10 年前

documentation Wiki

Tom Barron 12 年前

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

Community Mohan Dere 8 年前

@elcool's solution

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} last row - first row + 1

David Sky 15 年前

Guidelines for restricting SELECT statements

Lucio Menci 8 年前

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

gregphplab 13 年前

http://gregphplab.com/serendipity/uploads/slides/DB2_PHP_Best_practices.pdf

Hector 8 年前

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.