熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

使用三層嵌套正確實現Oracle分頁

2013-11-13 16:21:16  來源: Oracle 

  原始記錄

  select t* trowid from t_stu t

  order by ts_birthday desc

  返回

  STU_ID S_NAME C_ID S_BIRTHDAY

  S 李四   C

  S   C

  S   C

  S   C

  S   C

  Oracle分頁查詢

  第一種兩層嵌套查詢(網上流行的一種錯誤)

  select * from

  (

  select rownum rt* from t_stu t

  )

  where r between and order by s_birthday desc

  返回

  R STU_ID S_NAME C_ID S_BIRTHDAY

   S 李四   C

   S 張三豐 C

   S 張三   C

  說明上面的結果可能與你的不一樣因為Oracle在沒有order by的情況是隨機選取記錄的名的記錄應該是

  STU_ID S_NAME C_ID S_BIRTHDAY

  S 李四   C

  S   C

  S   C

  第二種換用三層嵌套查詢

  select b* from

  (select rownum ra* from

  (select t* from t_stu t order by s_birthday desc )a

  )b

  where br between and

  返回

  R STU_ID S_NAME C_ID S_BIRTHDAY

   S 李四   C

   S   C

   S   C

  正確!

  所以Oracle分布查詢一定要用三層嵌套步驟如下

  第三層分頁過濾

  select b*

  from (

  第二層給定行號

  select rownum ra* from (

  第一層排序

  select * from 表 order by 字段

  ) a

  where rownum<=最大行

  )b

  where br between 最小行 and 最大行

  ★★★關鍵點先排序後給行號兩個步驟要分開!

  為了程序的通用性對任意數據集都能分頁利用子查詢改為如下結構

  第三層分頁過濾

  select b*

  from (

  第二層給定行號

  select rownum ra* from (

  第一層排序

  select * from (一個已經排序的數據集)

  ) a

  where rownum<=最大行

  )b

  where br between 最小行 and 最大行

  如上面的查詢改為

  第三層分頁過濾

  select b*

  from (

  第二層給定行號

  select rownum ra* from (

  第一層排序

  select * from (select t* from t_stu t order by s_birthday desc)

  ) a

  where rownum<=最大行

  )b

  where br between 最小行 and 最大行

  或者其它查詢語句

  第三層分頁過濾

  select b*

  from (

  第二層給定行號

  select rownum ra* from (

  第一層排序

  select * from (select t* from   新聞表 t order by 發貼日期 desc)

  ) a

  where rownum<=最大行

  )b

  where br between 最小行 and 最大行

  ★原始數據數據腳本(請在命令窗口中粘貼以下語句即可)

  prompt PL/SQL Developer import file

  prompt Created on 日 星期一 by Administrator

  set feedback off

  set define off

  prompt Dropping T_STU

  drop table T_STU cascade constraints;

  prompt Creating T_STU

  create table T_STU

  (

  STU_ID     CHAR()

  S_NAME     CHAR()

  C_ID       CHAR()

  S_BIRTHDAY DATE

  S_SEX       CHAR()

  )

  ;

  prompt Disabling triggers for T_STU

  alter table T_STU disable all triggers;

  prompt Loading T_STU

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S 張三   C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S 李四   C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S 張三豐 C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  insert into T_STU (STU_ID S_NAME C_ID S_BIRTHDAY S_SEX)

  values (S    C to_date( ddmmyyyy) null);

  commit;

  prompt records loaded

  prompt Enabling triggers for T_STU

  alter table T_STU enable all triggers;

  set feedback on

  set define on

  prompt Done


From:http://tw.wingwit.com/Article/program/Oracle/201311/18196.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.