本文介绍了AS400/DB2中的分页查询(SQL)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

限时删除!!

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

I have been working on try to create php web-based paging for our tableswhich 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 fetchfirst 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 appreciatehelp 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)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

1403页,肝出来的..

09-07 13:42