oracle分页查询笔记

-- 分页查询
--查询员工表当中 salary前10的员工信息
 
SELECT * FROM (
							select rownum as rn ,LAST_NAME,SALARY 
							FROM (
										select LAST_NAME,SALARY 
										from EMPLOYEES 
										ORDER BY SALARY desc
										)
							)
WHERE rn <= 10 

-- 查询员工表当中 salary 10-20 的员工信息

SELECT * FROM (
							select rownum as rn ,LAST_NAME,SALARY 
							FROM (
										select LAST_NAME,SALARY 
										from EMPLOYEES 
										ORDER BY SALARY desc
										)
							)
WHERE rn >= 10 and rn <= 20


-- 分页查询  0 - 10   10 - 20   
/**
					0										10
	 RN <= (1-1) * 10 AND RN >= 1 * 10
					10									20
	 RN <= (2-1) * 10 AND RN >= 2 * 10
	 
*/
SELECT * FROM (
							SELECT ROWNUM AS RN,LAST_NAME,SALARY 
							FROM ( 
										SELECT LAST_NAME,SALARY 
										FROM EMPLOYEES
										ORDER BY SALARY DESC
										) 
							)
WHERE RN >= (pageNum-1) * pageSize  AND RN <= pageNum * pageSize


-- 查询第5页 每页10条记录

SELECT * FROM (
							SELECT ROWNUM AS RN,LAST_NAME,SALARY 
							FROM ( 
										SELECT LAST_NAME,SALARY 
										FROM EMPLOYEES
										ORDER BY SALARY DESC
										) 
							)
WHERE RN >= (5-1) * 10  AND RN <= 5 * 10