AS400/DB2 中的分页查询(SQL)

2022-01-14 00:00:00 sql db2 ibm-midrange php

我一直在努力尝试为我们的表格创建基于 web 的 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 起作用.我的第一页显示为"select * from accout order by acct 仅获取前 10 行"下一页"select * from account where acct>(last record) order by acct fetch仅限前10名"最后一页记录"select * from account where acct=(select max(acct) from account)"

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 account 来确定您将拥有多少页.

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

另请阅读 最快/最有效的方式使用 SQL 搜索 DB2 进行分页

相关文章