oracle ----sql语句

需求：

A：交易表

B：提现表

交易表中的提现数据状态就一个，要确定该提现最终状态需要到提现表里面根据该订单的billno匹配对应的提现数据，该sql语句查询的是所有成功的订单信息分页【其中包含提现成功和其他操作成功】

SELECT C.*
  FROM (SELECT B.*
          FROM (SELECT A.*, rownum rn
                  FROM (select TblWebBillDtl.SEQ_NO             as seqNo,
                               TblWebBillDtl.INT_USER_ID        as intUserId,
                               TblWebBillDtl.BUSS_TYPE          as bussType,
                               TblWebBillDtl.BUSS_CODE          as bussCode,
                               TblWebBillDtl.TRAN_TYPE          as tranType,
                               TblWebBillDtl.PUT_BRH_ID         as putBrhId,
                               TblWebBillDtl.BILL_BRH_ID        as billBrhId,
                               TblWebBillDtl.BILL_NO            as billNo,
                               TblWebBillDtl.EBPP_NO            as ebppNo,
                               TblWebBillDtl.BARCODE            as barcode,
                               TblWebBillDtl.PAY_MONTH          as payMonth,
                               TblWebBillDtl.RECORD_TIMES       as recordTimes,
                               TblWebBillDtl.ACNT_DATE          as acntDate,
                               TblWebBillDtl.SEND_DATE          as sendDate,
                               TblWebBillDtl.PAY_DATE           as payDate,
                               TblWebBillDtl.BILL_PAY_DATE      as billPayDate,
                               TblWebBillDtl.LAST_PAY_DATE      as lastPayDate,
                               TblWebBillDtl.BILL_BEGIN_DATE    as billBeginDate,
                               TblWebBillDtl.BILL_END_DATE      as billEndDate,
                               TblWebBillDtl.CONTRACT_NO        as contractNo,
                               TblWebBillDtl.EQUIP_NO           as equipNo,
                               TblWebBillDtl.CURRENCY           as currency,
                               TblWebBillDtl.BILL_AMT           as billAmt,
                               TblWebBillDtl.NEED_PAY_AMT       as needPayAmt,
                               TblWebBillDtl.PAY_AMT            as payAmt,
                               TblWebBillDtl.DELAY_AMT          as delayAmt,
                               TblWebBillDtl.FEE_AMT            as feeAmt,
                               TblWebBillDtl.PUNISH_AMT         as punishAmt,
                               TblWebBillDtl.BILL_CREATE_TM     as billCreateTm,
                               TblWebBillDtl.BILL_PAY_TM        as billPayTm,
                               TblWebBillDtl.BILL_NAME          as billName,
                               TblWebBillDtl.BILL_ADDRESS       as billAddress,
                               TblWebBillDtl.BILL_ZIP           as billZip,
                               TblWebBillDtl.CERT_TYPE          as certType,
                               TblWebBillDtl.CERT_NO            as certNo,
                               TblWebBillDtl.PHONE_NO           as phoneNo,
                               TblWebBillDtl.BILL_STATUS        as billStatus,
                               TblWebBillDtl.BILL_DTL           as billDtl,
                               TblWebBillDtl.MISC               as misc,
                               TblWebBillDtl.RESERVED1          as reserved1,
                               TblWebBillDtl.RESERVED2          as reserved2,
                               TblWebBillDtl.RESERVED3          as reserved3,
                               TblWebBillDtl.RESERVED4          as reserved4,
                               TblWebBillDtl.RESERVED5          as reserved5,
                               TblWebBillDtl.RESERVED6          as reserved6,
                               TblWebBillDtl.RESERVED7          as reserved7,
                               TblWebBillDtl.LAST_UPD_OPRID     as lastUpdOprid,
                               TblWebBillDtl.LAST_UPD_TRANSCODE as lastUpdTranscode,
                               TblWebBillDtl.LAST_UPD_TM        as lastUpdTm,
                               TblWebBillDtl.ORDER_ID           as orderId,
                               TblWebBillDtl.PAY_ORG_NAME       as payOrgName
                          FROM portal.TBL_WEB_BILL_DTL               TblWebBillDtl,
                               coresys.TBL_CSYS_WITHDRAW_CASH TblCsysWithdrawCash
                         WHERE TblWebBillDtl.INT_USER_ID = '0000000577'
                         and TblWebBillDtl.BILL_NO = TblCsysWithdrawCash.BILL_NO(+)
                         and  TblWebBillDtl.BILL_STATUS = '01'
                       
                         and (TblCsysWithdrawCash.STATUS = '02' or TblCsysWithdrawCash.STATUS is null)
                        
                         order by TblWebBillDtl.BILL_CREATE_TM desc) A) B) C
 WHERE C.rn <= 1000
   and C.rn > 0