oracle 分页查询sql语句

1.

SELECT t.t_credit_id as creditId,
       t.login_name  as loginName,
       t.long_pwd    as loginPwd,
       t.agin_pwd    as againPwd
  FROM (SELECT tc.t_credit_id,
               tc.login_name,
               tc.long_pwd,
               tc.agin_pwd,
               ROWNUM rn
          FROM t_credit tc
         where 1=1
         and ROWNUM <=10
         and tc.login_name like '%lisi%' --如查询条件写在里面是先分页在查询，写在外面先查询在分页
         ORDER BY tc.crete_time DESC) t
 WHERE t.rn >= 1;

2.

SELECT t.t_credit_id            as creditId,
       t.login_name             as loginName,
       t.long_pwd               as loginPwd,
       t.agin_pwd               as againPwd,
       t.id_card                as IDCard,
       t.fixed_telephone_number as fixedTelephoneNumber,
       t.telephone_number       as telephoneNumber,
       t.e_mail                 as email,
       t.address                as address,
       t.crete_time             as createtime,
       t.status                 as status
  FROM (SELECT tc.t_credit_id,
               tc.login_name,
               tc.long_pwd,
               tc.agin_pwd,
               tc.id_card,
               tc.fixed_telephone_number,
               tc.telephone_number,
               tc.e_mail,
               tc.address,
               tc.crete_time,
               tc.status,
               ROWNUM rn
               FROM t_credit tc
           ) t
 WHERE t.rn >= 1
   AND t.rn <= 20
   and t.login_name like '%l%'
 ORDER BY t.crete_time DESC;

3.

SELECT * FROM news_detail;

--每页显示2条新闻信息，查看第1页新闻信息（新闻标题、作者、创建时间）的分页sql语句如下
--页码：currPageNo 1 =(currPageNo-1)*PAGESIZE+1 每页显示的记录数：pageSize 此例=2 =CURRPAGENO * PAGESIZE
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=1 AND a.rn<=2

--每页显示2条新闻信息，查看第2页新闻信息（新闻标题、作者、创建时间）的分页sql语句如下
--页码：currPageNo 2  每页显示的记录数：pageSize 此例=2
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=3 AND a.rn<=4

--每页显示2条新闻信息，查看第3页新闻信息（新闻标题、作者、创建时间）的分页sql语句如下
        --页码：currPageNo 3   每页显示的记录数：pageSize 此例=2
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=5 AND a.rn<=6

--起始记录数和结束记录数有什么特点？和页码与每页显示的记录有什么关系？
--页码：currPageNo   每页显示的记录数：pageSize 此例=2
SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
WHERE a.rn>=(currPageNo - 1) * pageSize + 1 AND a.rn<=currPageNo * pageSize