Oracle 排序分页查询和 MySQL 数据库的语句还不一样，这里做简单的记录。
按操作时间排序 1
SELECT A.*, ROWNUM RN FROM (SELECT * FROM v_log) A ORDER BY operatetime DESC
结果
可以发现，按时间排序了，但是 rownum 并不是从小到大，因为 oracle 是先生成 rownum，再进行排序，需要在套一层查询
按操作时间排序 2
SELECT T.*, rownum RN FROM(
SELECT * FROM (SELECT * FROM v_log) ORDER BY operatetime DESC
) T
结果：
顺序正确，rownum 正确，在此基础上再套一层查询进行分页
按操作时间排序并分页
SELECT T2.* from(
SELECT T.*, rownum RN FROM(
SELECT * FROM (SELECT * FROM v_log) ORDER BY operatetime DESC
)T) T2 WHERE RN BETWEEN 1 and 10
测试
SELECT * FROM (
SELECT A.”sku”, ROWNUM rn, A.”goods_sn”
FROM AMZ_HUOPIN_SKU A
WHERE ROWNUM <= 10 ORDER BY A.”goods_sn” DESC) temp
WHERE temp.rn > 0;
SELECT A.”sku”, A.”goods_sn”, ROWNUM RN FROM AMZ_HUOPIN_SKU A ORDER BY A.”sku” DESC
## 子查询先找出所有，然后再 rownum,rownum 为伪列，后再排序
SELECT A.”sku”, A.”goods_sn”, ROWNUM RN FROM (SELECT * FROM AMZ_HUOPIN_SKU) A ORDER BY A.”sku” DESC
## 因为 oracle 是先生成 rownum，再进行排序，需要在套一层查询, 即先拍好序，然后再生成 rownum
SELECT T.”sku”, T.”goods_sn”, ROWNUM RN FROM (SELECT * FROM (SELECT * FROM AMZ_HUOPIN_SKU) ORDER BY “sku” DESC
) T
## 上边的这两个语句是等价的
SELECT T.”sku”, T.”goods_sn”, ROWNUM RN FROM (SELECT * FROM AMZ_HUOPIN_SKU ORDER BY “sku” DESC
) T
SELECT T2.* FROM(
SELECT T.”sku”, T.”goods_sn”, ROWNUM RN FROM (SELECT * FROM AMZ_HUOPIN_SKU ORDER BY “sku” DESC
) T) T2 WHERE RN BETWEEN 0 AND 10
注：本文为转载，原文地址：oracle 先排序再分页