oracle先排序再分页

悠悠 2022-04-23 14:22 234阅读 0赞

> Oracle排序分页查询和MySQL数据库的语句还不一样，这里做简单的记录。

## 按操作时间排序1 ##

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

结果

![clipboard.png][]

可以发现，按时间排序了，但是rownum并不是从小到大，因为oracle是先生成rownum，再进行排序，需要在套一层查询

## 按操作时间排序2 ##

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

结果：

![clipboard.png][clipboard.png 1]  
顺序正确，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

![clipboard.png][clipboard.png 2]

## 测试 ##

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

## 综合查询 ##

# 1、子句查询，这条语句可以加条件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、排序
    
    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、排序之后，再获取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、再根据获取到的ROWNUM按照顺序进行分页
    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

--------------------

注：本文为转载，原文地址：[oracle先排序再分页][oracle]

[clipboard.png]: /images/20220308/fc392710f8844a8cbbec3eb79bb623b9.png
[clipboard.png 1]: /images/20220308/de587ab53e8345998df8c7b382022e31.png
[clipboard.png 2]: /images/20220308/1797ae532a494cb994bf1db8c53f6040.png
[oracle]: https://www.cnblogs.com/lurenjia1994/p/9558663.html