需求:
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