oracle mysql SqlServer 数据库分页实现sql_oracle分页 currentpage++ sqlresult-优快云博客

本文链接：https://blog.youkuaiyun.com/xiaogg3678/article/details/38337959

本文介绍了在MySQL、Oracle和SQL Server中实现分页查询的具体方法，包括构造SQL语句的Java代码示例。针对不同数据库的特点，提供了适用于各自平台的分页解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql的分页实现：

[java] view plain copy

public StringBuilder buildPageSqlForMysql(String sql, PageParameter page) {

StringBuilder pageSql = new StringBuilder(100);

String beginrow = String.valueOf((page.getCurrentPage() - 1) * page.getPageSize());
pageSql.append(sql);
pageSql.append(" limit " + beginrow + "," + page.getPageSize());
return pageSql;
}

String beginrow = String.valueOf((page.getCurrentPage() - 1) * page.getPageSize());

limit " + beginrow + "," + page.getPageSize()

oracle的分页实现：

[java] view plain copy

public StringBuilder buildPageSqlForOracle(String sql, PageParameter page) {
StringBuilder pageSql = new StringBuilder(100);
String beginrow = String.valueOf((page.getCurrentPage() - 1) * page.getPageSize());
String endrow = String.valueOf(page.getCurrentPage() * page.getPageSize());
pageSql.append("select * from ( select temp.*, rownum row_id from ( ");
pageSql.append(sql);
pageSql.append(" ) temp where rownum <= ").append(endrow);
pageSql.append(") where row_id > ").append(beginrow);
return pageSql;
}

String beginrow = String.valueOf((page.getCurrentPage() - 1) * page.getPageSize());
String endrow = String.valueOf(page.getCurrentPage() * page.getPageSize());
pageSql.append("select * from ( select temp.*, rownum row_id from ( ");

pageSql.append(" ) temp where rownum <= ").append(endrow);
pageSql.append(") where row_id > ").append(beginrow);

sqlserver 分页sql:

分页方案一：(利用Not In和SELECT TOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID

-------------------------------------

分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID