-分页参数：size = 20 page = 2

--没有order by的查询

-- 嵌套子查询，两次筛选（推荐使用）

--SELECT *

-- FROM (SELECT ROWNUM AS rowno, t.*

-- FROM DONORINFO t

-- WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')

-- AND TO_DATE ('20060731', 'yyyymmdd')

-- AND ROWNUM <= 20*2) table_alias

-- WHERE table_alias.rowno > 20*(2-1);        --耗时0.05s

-- 一次筛选（数据量大的时候，第一次查询的数据量过大，明显比上面慢，不推荐）

--select * from(

--SELECT ROWNUM AS rowno, t.*

--FROM DONORINFO t

--WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd') AND TO_DATE ('20060731', 'yyyymmdd')

--) r

--where r.rowno BETWEEN 20*(2-1)+1 and 20*2;      --耗时0.46s

--有order by的查询

--嵌套子查询，两次筛选（推荐使用）

--SELECT *

--FROM (SELECT ROWNUM AS rowno,r.*

-- FROM(

-- SELECT * FROM DONORINFO t

-- WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')

-- AND TO_DATE ('20060731', 'yyyymmdd')

-- ORDER BY t.BIRTHDAY desc

-- ) r

-- where ROWNUM <= 20*2

-- ) table_alias

-- WHERE table_alias.rowno > 20*(2-1);                --耗时0.744s

-- 一次筛选（数据量大的时候，第一次查询的数据量过大，明显比上面慢，不推荐）

--select * from (

--SELECT ROWNUM AS rowno,r.*

--FROM(

--SELECT * FROM DONORINFO t

--WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')

--AND TO_DATE ('20060731', 'yyyymmdd')

--ORDER BY t.BIRTHDAY desc

--) r

----where ROWNUM <= 20; --这里用>查不到数据 =也查不到数据 <= 或者 < 可以查到数据

----where ROWNUM BETWEEN 20*(2-1)+1 AND 20*2; --查不到数据

----where ROWNUM <=20*2 and ROWNUM > 20*(2-1); --查不到数据

----这是因为查询时，第一条生成的rownum为1,1>20不成立，1=20也不成立，所以这条数据就作废了，依次类推，这样就查不到任何一条数据

--) t

--where t.rowno <=20*2 and t.rowno > 20*(2-1); --可以查到数据耗时：3.924s

---- where t.rowno BETWEEN 20*(2-1)+1 AND 20*2; --可以查到数据耗时：3.919s

--采用row_number() over 分页函数

--select *

--from(select d.*,row_number() over(order by d.BIRTHDAY) as rownumber

--     from DONORINFO d

--     WHERE d.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')

--                             AND TO_DATE ('20060731', 'yyyymmdd')

-- ) p

--where p.rownumber BETWEEN 20*(2-1)+1 AND 20*2;  --耗时0.812s

select * from (

select *

from(select d.*,row_number() over(order by d.BIRTHDAY) as rownumber

     from DONORINFO d

     WHERE d.BIRTHDAY BETWEEN TO_DATE ('', 'yyyymmdd')

                             AND TO_DATE ('', 'yyyymmdd')

 ) p

where p.rownumber <20*2

) where rownumber > 20*(2-1);    -- 耗时0.813s

SELECT *

FROM (SELECT ROWNUM AS rowno, t.*

          FROM DONORINFO t

          WHERE t.BIRTHDAY BETWEEN TO_DATE ('', 'yyyymmdd')

          AND TO_DATE ('', 'yyyymmdd')

          AND ROWNUM <= page*size) table_alias

WHERE table_alias.rowno > (page-1)*size;

SELECT *

FROM (SELECT ROWNUM AS rowno,r.*

           FROM(SELECT * FROM DONORINFO t

                    WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')

                    AND TO_DATE ('20060731', 'yyyymmdd')

                    ORDER BY t.BIRTHDAY desc

                   ) r

           where ROWNUM <= page*size

          ) table_alias

WHERE table_alias.rowno > (page-1)*size;