Question

ORACLE笔试题：存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql

Answer 1

1.select * from (select c.*,rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30;

2.select * from (select * from test order by c desc) x where rownum < 30
minus
select * from (select * from test order by c desc) y where rownum < 20 order by 3 desc
二者相比，下边的Minus性能稍差。

Answer 2

SELECT
a, b, c, d
FROM
(SELECT rownum AS no, a,b,c,d FROM T ORDER BY c) as tmpView
WHERE
no >= 21 AND no <30

Answer 3

SELECT a,b,c,d FROM T ORDER BY c DESC LIMIT 20,10