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
综合查询
# 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先排序再分页
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/101100.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...