常用数据库分页显示

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;