Oracle sort paging query and MySQL database statements are not the same, here is a simple record.

Sort by operation time 1

SELECT A.*, ROWNUM RN FROM (SELECT * FROM v_log) A ORDER BY operatetime DESC 

Result

It can be found that it is sorted by time, but rownum is not from small to large, because oracle becomes rownum, and then it needs to query in a set of layers

Sort by operation time 2

SELECT T.*, rownum RN FROM(
      SELECT * FROM (SELECT * FROM v_log) ORDER BY operatetime DESC 
    ) T

Result:

The order is correct, and rownum is correct. On this basis, another layer of query is set for paging

Sort and page by operation time

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

test

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


## The subquery finds all of them first, and then rownum. Rownum is a pseudo column, and then it is sorted
SELECT A."sku", A."goods_sn", ROWNUM RN FROM (SELECT * FROM AMZ_HUOPIN_SKU) A ORDER BY A."sku" DESC

## Because oracle generates rownum, and then sorts it, it needs to query in a set of layers, that is, first take the order, and then generate rownum
SELECT T."sku", T."goods_sn", ROWNUM RN FROM (SELECT * FROM (SELECT * FROM AMZ_HUOPIN_SKU) ORDER BY "sku" DESC
) T

## The two statements above are equivalent
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

Comprehensive query

# 1. Clause query. This statement can be conditional WHERE
SELECT * FROM (SELECT A."sku", A."goods_sn", A."category",A."color_name", A."size_name", A."fnsku",H.QTY,H.WAREHOUSEID,H.F_CREATE_TIME FROM AMZ_HUOPIN_SKU A 
LEFT JOIN HWC_CAMEL_INV_ALL H ON A."fnsku" = H.SKU)

# 2, sort

SELECT * FROM (SELECT * FROM (SELECT A."sku", A."goods_sn", A."category",A."color_name", A."size_name", A."fnsku",H.QTY,H.WAREHOUSEID,H.F_CREATE_TIME FROM AMZ_HUOPIN_SKU A 
LEFT JOIN HWC_CAMEL_INV_ALL H ON A."fnsku" = H.SKU)) ORDER BY "goods_sn" DESC

# 3. After sorting, get ROWNUM
SELECT T.*, ROWNUM RN FROM (SELECT * FROM (SELECT * FROM (SELECT A."sku", A."goods_sn", A."category",A."color_name", A."size_name", A."fnsku",H.QTY,H.WAREHOUSEID,H.F_CREATE_TIME FROM AMZ_HUOPIN_SKU A 
LEFT JOIN HWC_CAMEL_INV_ALL H ON A."fnsku" = H.SKU)) ORDER BY "goods_sn" DESC) T

# 4. Paginate in order according to the ROWNUM obtained
SELECT R.* FROM(
  SELECT T.*, ROWNUM RN FROM (SELECT * FROM (SELECT * FROM (SELECT A."sku", A."goods_sn", A."category",A."color_name", A."size_name", A."fnsku",H.QTY,H.WAREHOUSEID,H.F_CREATE_TIME FROM AMZ_HUOPIN_SKU A 
  LEFT JOIN HWC_CAMEL_INV_ALL H ON A."fnsku" = H.SKU WHERE A."goods_sn" = 'K832026565')) ORDER BY "goods_sn" DESC) T
) R WHERE RN BETWEEN 10 AND 20

Note: This is a reprint. The original address is: oracle sort before Pagination