常用数据库分页显示

最新推荐文章于 2024-11-08 14:07:13 发布

Cary_ZZ

最新推荐文章于 2024-11-08 14:07:13 发布

阅读量106

点赞数

分类专栏：日常技术总结文章标签：数据库分页 sql

本文链接：https://blog.csdn.net/zz614/article/details/84231265

版权

日常技术总结专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SQL分页样例

---ORACLE分页查询------
SELECT * FROM(SELECT A.*, ROWNUM RN FROM (SELECT RES_OBJECT.* FROM RES_OBJECT,RES_NODE WHERE RES_OBJECT.RES_ID=RES_NODE.RES_ID) A WHERE ROWNUM <=4) WHERE RN>= 3;

----MYSQL查询语句------
SELECT * FROM (SELECT RES_OBJECT.* FROM RES_OBJECT, RES_NODE WHERE RES_OBJECT.RES_ID=RES_NODE.RES_ID ) A LIMIT 0,25;
-----SQLSERSER分页查询------------
----SQLSERVER2000----------------
SELECT C.* FROM(SELECT TOP 2 B.* FROM(SELECT TOP 4 A.* FROM (SELECT RES_OBJECT.* FROM RES_OBJECT,RES_NODE WHERE RES_OBJECT.RES_ID=RES_NODE.RES_ID) A ORDER BY A.RES_ID) B ORDER BY B.RES_ID DESC) C ORDER BY C.RES_ID;
----SQLSERVER2005----------
declare @pagesize int,@pageNum int
set @pagesize=5
set @pageNum=3

select A.* from (
select RES_OBJECT.*,row_number() over(order by RES_OBJECT.RES_ID) rn
from RES_OBJECT,RES_NODE WHERE RES_OBJECT.RES_ID=RES_NODE.RES_ID) A
where rn between @pagesize*(@pageNum-1)+1 and @pagesize*(@pageNum)

-----Sybase分页查询--------
declare @num numeric(18,0)
set rowcount 6
select @num=RES_OBJECT.RES_ID FROM RES_OBJECT,RES_NODE WHERE RES_OBJECT.RES_ID=RES_NODE.RES_ID ORDER BY RES_OBJECT.RES_ID
set rowcount 4
SELECT RES_OBJECT.* FROM RES_OBJECT,RES_NODE WHERE RES_OBJECT.RES_ID=RES_NODE.RES_ID AND RES_OBJECT.RES_ID>=@num ORDER BY RES_OBJECT.RES_ID;