Oracle分页查询

SELECT  * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM bhis_gz_zg_360007002204.v_query_lis_pcas) A 
)
WHERE RN BETWEEN 1 AND 10
　　　　　　　　　　11　　 20

　　　　　　　　　　21　　 30

oracle没有limit，有rownum记录行号， 需要参数都需要变， BETWEEN  i*n-n+1  AND  i*n；查询计算：当前页码：i，当前页面大小：n

 

上面那种效率差并且不能排序

-- 	有ORDER BY排序的写法。(效率较高)

	SELECT *
  FROM (SELECT tt.*, ROWNUM AS n
          FROM ( SELECT  ID, TZBT, TZNR, SWRYJGMC, to_char(TZSJ, 'yyyy-MM-dd HH:mm:ss')as tzsj from tb_plsb_xxfw
        WHERE YXBZ='Y'     AND TZRZH = 'kz0419' 
				and TZSJ >= TO_DATE('2022-05-28','yyyy-MM-dd')
               ORDER BY TZSJ DESC) tt
         WHERE ROWNUM <= 10) tb_plsb_xxfw
 WHERE tb_plsb_xxfw.n >= 1;