- 定义四个分页变量
int pageNow | 表示第几页 | 该变量由用户决定 |
int pageSize | 表示每一页显示几条记录 | 由程序指定(也可由用户定制) |
int pageCont | 表示共有多少页 | 该变量是计算出来的->>算法 |
int rowCont | 表示共有多少条记录 | 该变量是查询数据库得到 |
- 分页算法.确定rowCont的值
pst = con.prepareStatement("select count(*) form users");
rst = pst.executeQuery();
rst.next();
rowCont = rst.getInt(1);
- 分页算法.确定pageCont的值
1.二逼青年算法
if (rowCont % pageSize == 0) {
pageCont = rowCont / pageSize;
} else {
pageCont = rowCont / pageSize + 1;
}
2.普通青年算法(等价与上面那个算法)
pageCont = rowCont % pageSize == 0 ? rowCont / pageSize : rowCont / pageSize + 1;
3.文艺青年算法
pageCont = (rowCont - 1)/pageSize + 1;
- 分页算法.SQLServer数据库查询
1.(利用Not In和SELECT TOP分页)
SELECT TOP pageSize *
FROM table1
WHERE id NOT IN
(
SELECT TOP ( pageSize *(pageNow-1)) id FROM table1 ORDER BY id
)
ORDER BY id
FROM table1
WHERE id NOT IN
(
SELECT TOP ( pageSize *(pageNow-1)) id FROM table1 ORDER BY id
)
ORDER BY id
2.(利用ID大于多少和SELECT TOP分页)
SELECT TOP
pageSize
*
FROM table1
WHERE id >
(
SELECT ISNULL(MAX(id),0)
FROM
(
SELECT TOP (pageSize*(pageNow-1)) id FROM table1 ORDER BY id
) A
)
ORDER BY id
WHERE id >
(
SELECT ISNULL(MAX(id),0)
FROM
(
SELECT TOP (pageSize*(pageNow-1)) id FROM table1 ORDER BY id
) A
)
ORDER BY id
3.经测试这个效率较高
SELECT TOP pageSize*
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
) A
WHERE RowNumber > pageSize *(pageNow-1)
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
) A
WHERE RowNumber > pageSize *(pageNow-1)