Oracle常见面试题及答案详解12

会计考友

12.  存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql
    SELECT   *
        FROM (SELECT ROWNUM AS row_num, tmp_tab.*
                FROM (SELECT   a, b, c, d
                          FROM T
                      ORDER BY c) tmp_tab
5 F5 j2 S7 f# }# m9 e( e" t: w9 g               WHERE ROWNUM = 20
ORDER BY row_num;
+ L6 d8 Z7 @8 r  
create table t(a number(,b number(,c number(,d number();
- q8 X, N$ N% F8 |, U/  
% n$ q# X8 n2 [; fbegin  
for i in 1 .. 300 loop  
insert into t values(mod(i,2),i/2,dbms_random.value(1,300),i/4);  
end loop;
$ `* ]- |* P" g" @5 Z0 t- Qend;  
/
+ z% z% N0 F" G. M6 Wselect * from (select c.*,rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30;
/
+ o# S( X# E2 T: e* G' cselect * from (select * from test order by c desc) x where rownum < 30
& C7 Q- J7 v/ J+ sminus
4 F" }  v9 |7 ?& x- L8 v& g( Lselect * from (select * from test order by c desc) y where rownum < 20 order by 3 desc  
. G) N# x& ?8 S0 C相比之 minus性能较差