oracle标准sql语句,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