AS400/DB2中的分页查询(SQL) [英] Pagination query(SQL) in AS400/DB2

问题描述

我一直在尝试为我们的表创建基于php的基于网页的分页超过一百万行.

I have been working on try to create php web-based paging for our tables which have over a million rows.

根据我的阅读，我有3个选择

Based on what I have read, I have 3 options

1. 检索结果集中的所有行-大小不可能为我
2. 检索1000行，存储在临时表中并为创建一个迭代器它并对其进行分页-查询太多-插入太多！
3. 如果有人选择向前或向后选择页面，则每次都运行查询

现在，我正在尝试使选项3正常工作.我的第一页显示为通过acct从accout顺序中选择*仅获取前10行"下一页从acct>(最后记录)按acct提取顺序的帐户中选择*仅前10个"页面最后一条记录从帐户中选择*，其中acct =(从帐户中选择max(acct))"

Right now I am trying to get option 3 working. I have the first page showing up as "select * from accout order by acct fetch first 10 rows only" Page next "select * from account where acct>(last record) order by acct fetch first 10 only" page last record "select * from account where acct=(select max(acct) from account)"

问题正在显示上一页，我非常感谢帮助.

The problem is showing the previous page and i really would appreciate help in this.

推荐答案

SELECT *
  FROM (
    SELECT 
      *, 
      ROW_NUMBER() OVER (ORDER BY acct) AS RowNum
    FROM 
      account
  ) AS Data
WHERE 
  RowNum BETWEEN 100 AND 110;

首先，您应该摆脱 SELECT * .仅选择您需要的字段.

First, you should get rid of the SELECT *. Select only the fields you need.

在 acct 上放置一个索引，这将有助于 ROW_NUMBER()OVER(ORDER BY acct)的构造.

Place an index on acct, this will help the ROW_NUMBER() OVER (ORDER BY acct) construct.

使用 SELECT COUNT(*)FROM帐户确定您将拥有多少页.

Use a SELECT COUNT(*) FROM account to determine how many pages you will have.

也请阅读最快最高效的方法用SQL搜索DB2进行分页

这篇关于AS400/DB2中的分页查询(SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！