关于as400(ISeries)的翻页语句的写法,好像对于海量的尤为有效

新闻组上面看到的
1.主机V5R4,也就是说I5的机器,有一个函数row_number() --这个因为自己的主机环境是V5R3 所以无从考证
  select * from (select row_number() over (order by id) as rn , * from table1 ) as te

2.主机是V5R3或者以下的可以使用如下语句
以下语句是取结果的16-20条

with x as (Select Col1, Col2, .... ColN from MyTable
              Where ...
              order by Key1, Key2, .... KeyN
              fetch first 20 rows only),   //这里获取前20条 用逗号分割
     y as (select * from x
              order by Key1 desc, Key2 desc, ... KeyN desc
              fetch first  5 rows only)   //获取前五条
select * from y
order by Key1, Key2 ... KeyN    //从第二次过滤中获取全部的数据,原理使用临时表

转载于:https://www.cnblogs.com/wildfish/archive/2006/07/21/456175.html