select *
from (select a.*, rownum as rn
from (select *
from (select t.ID,
t.OUT_TRADE_NO as outTradeNo,
t.TRADE_TYPE as tradeType,
t.PARTNER_ID as partnerId,
t.BUSINESS_TYPE as businessType,
t.GOODS_NAME as goodsName,
t.TRADE_AMOUNT as tradeAmount,
t.REFUND_TOTAL_AMOUNT as refundTotalAmount,
t.BUYER_USER_ID as buyerUserId,
t.SELLER_USER_ID as sellerUserId,
t.PAY_ORDER_NO as payOrderNo,
t.TRADE_STATUS as tradeStatus,
t.BODY,
t.REFUND_STATUS as refundStatus,
t.ROYALTY_PARAMETERS as royaltyParameters,
t.RELATED_OUT_TRADE_NO as relatedOutTradeNo,
t.ERROR_MEMO as errorMemo,
t.MEMO1,
t.GMT_CREATE as gmtCreate,
t.CONFIRM_DATE as confirmDate,
t.GMT_MODIFIED as gmtModified,
t.PAY_DATE as payDate,
t.REFUND_DATE as refundDate,
t.CONFIRM_STATUS as confirmStatus,
t.RETURN_REASON as returnReason,
t.TRADE_CREATER as tradeCreater,
t.SHIPING,
t.GOODS_AMOUNT as goodsAmount,
t.FIEIGHT_AMOUNT as fieightAmount,
t.TRADE_REFUND_STATUS as tradeRefundStatus,
t.AUTOSENDGOODSTIME,
t.AUTOCONFIRMFIXEDVALUE,
t.DELIVERY_DATE as deliveryDate,
t.R_REFUND_STATUS as rRefundStatus,
t.DELAY_STATUS as delayStatus,
t.DELAY_TYPE as delayType,
t.APP_STATUS as appStatus,
t.APP_HANDLE_STATUS as appHandleStatus,
t.APP_BUYER_STATUS as appBuyerStatus,
t.APP_SELLER_STATUS as appSellerStatus,
t.BUYER,
t.SELLER,
t.BUYERMOBILE,
t.SELLERMOBILE,
t.buyer_show_status as buyerShowStatus,
t.seller_show_status as sellerShowStatus,
(case
when ((t.BUYER_USER_ID = ? and
t.TRADE_TYPE <> '03') or
(t.SELLER_USER_ID = ? and
t.TRADE_TYPE = '03')) then
-1
else
1
end) as IN_OR_OUT
from PAY_ORDERBASE_VIEW t
where (t.BUYER_USER_ID = ?)
and (t.buyer_show_status = ?)
and (t.BUYER_USER_ID = ? or t.SELLER_USER_ID = ?)
UNION
select t.ID,
t.OUT_TRADE_NO as outTradeNo,
t.TRADE_TYPE as tradeType,
t.PARTNER_ID as partnerId,
t.BUSINESS_TYPE as businessType,
t.GOODS_NAME as goodsName,
t.TRADE_AMOUNT as tradeAmount,
t.REFUND_TOTAL_AMOUNT as refundTotalAmount,
t.BUYER_USER_ID as buyerUserId,
t.SELLER_USER_ID as sellerUserId,
t.PAY_ORDER_NO as payOrderNo,
t.TRADE_STATUS as tradeStatus,
t.BODY,
t.REFUND_STATUS as refundStatus,
t.ROYALTY_PARAMETERS as royaltyParameters,
t.RELATED_OUT_TRADE_NO as relatedOutTradeNo,
t.ERROR_MEMO as errorMemo,
t.MEMO1,
t.GMT_CREATE as gmtCreate,
t.CONFIRM_DATE as confirmDate,
t.GMT_MODIFIED as gmtModified,
t.PAY_DATE as payDate,
t.REFUND_DATE as refundDate,
t.CONFIRM_STATUS as confirmStatus,
t.RETURN_REASON as returnReason,
t.TRADE_CREATER as tradeCreater,
t.SHIPING,
t.GOODS_AMOUNT as goodsAmount,
t.FIEIGHT_AMOUNT as fieightAmount,
t.TRADE_REFUND_STATUS as tradeRefundStatus,
t.AUTOSENDGOODSTIME,
t.AUTOCONFIRMFIXEDVALUE,
t.DELIVERY_DATE as deliveryDate,
t.R_REFUND_STATUS as rRefundStatus,
t.DELAY_STATUS as delayStatus,
t.DELAY_TYPE as delayType,
t.APP_STATUS as appStatus,
t.APP_HANDLE_STATUS as appHandleStatus,
t.APP_BUYER_STATUS as appBuyerStatus,
t.APP_SELLER_STATUS as appSellerStatus,
t.BUYER,
t.SELLER,
t.BUYERMOBILE,
t.SELLERMOBILE,
t.buyer_show_status as buyerShowStatus,
t.seller_show_status as sellerShowStatus,
(case
when ((t.BUYER_USER_ID = ? and
t.TRADE_TYPE <> '03') or
(t.SELLER_USER_ID = ? and
t.TRADE_TYPE = '03')) then
-1
else
1
end) as IN_OR_OUT
from PAY_ORDERBASE_VIEW t
where (t.SELLER_USER_ID = ?)
and (t.seller_show_status = ?)
and (t.BUYER_USER_ID = ? or t.SELLER_USER_ID = ?))
order by id asc) a
where rownum <= ?) b
where rn >= ?