问题描述
我一直在努力尝试为我们的表格创建基于 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
- 检索结果集中的所有行 - 对我来说不可能，因为它的大小
- 检索 1000 行，存储在临时表中并为它并通过它翻页 - 查询太多 - 插入太多！！
- 如果有人选择向前或向后翻页，则每次运行查询
现在我正在尝试使选项 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 进行分页
这篇关于AS400/DB2 中的分页查询(SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！