sql總結
- 場景1分組后失敗的成功數據帶入
- 場景2完全性質的一對一匹配
- 場景3虛擬戶的特殊匹配
- 場景4多對多匹配
- 場景5一對一匹配
- 場景6 一對多匹配
場景1分組后失敗的成功數據帶入
現有一批交易表的數據，根據戶名，日期，金額分組，存在TRADE_STATUS 有成功和失敗的情況，
而失敗的FUNDFLOW_DETAILS_ID字段已和資金表的FUNDFLOW_DETAILS_ID匹配。
現需要將成功的憑證號和期間根據";"拼接到一起，存儲到與該組失敗數據匹配的資金表的數據中，存儲到JOURNAL_VOUCHER_NUMBER和ACCOUNT_DATE 字段
使用
HAVING
sum(CASE WHEN a.TRADE_STATUS = ‘失敗’ THEN 1 ELSE 0 END) >=1
AND
sum(CASE WHEN a.TRADE_STATUS = ‘成功’ THEN 1 ELSE 0 END) >=1
判斷該分組下是否至少有一條成功的數據和一條失敗的數據
DECLARE CURSOR res_data IS SELECT a.CUSTOMER_ACCOUNT_NAME_ORIGIN,TRUNC(a.TRADE_DATE_BAK) AS ts_date,a.TRADE_AMOUNTFROM RC_TRADEFLOW_DETAILS_NAPR aWHERE 1=1AND a.OURS_ACCOUNT_NUMBER LIKE '%02222' GROUP BY a.CUSTOMER_ACCOUNT_NAME_ORIGIN,TRUNC(a.TRADE_DATE_BAK),a.TRADE_AMOUNT HAVING sum(CASE WHEN a.TRADE_STATUS = '失敗' THEN 1 ELSE 0 END) >=1ANDsum(CASE WHEN a.TRADE_STATUS = '成功' THEN 1 ELSE 0 END) >=1;a_vouchers varchar2(4000);a_periods varchar2(4000);
BEGIN FOR s IN res_data LOOPSELECT EXTRACT(XMLAGG(XMLELEMENT(e, pz, ';') ORDER BY pz),'/E/text()').GETCLOBVAL() INTO a_vouchersFROM (SELECT DISTINCT a.JOURNAL_VOUCHER_NUMBER as pzFROM RC_TRADEFLOW_DETAILS_NAPR a WHERE 1=1AND a.OURS_ACCOUNT_NUMBER LIKE '%2222'AND a.CUSTOMER_ACCOUNT_NAME_ORIGIN = s.CUSTOMER_ACCOUNT_NAME_ORIGINAND TRUNC(a.TRADE_DATE_BAK) = s.ts_dateAND a.TRADE_AMOUNT = s.TRADE_AMOUNTAND a.TRADE_STATUS = '成功');SELECT EXTRACT(XMLAGG(XMLELEMENT(e, qj, ';') ORDER BY qj),'/E/text()').GETCLOBVAL() INTO a_periodsFROM (SELECT DISTINCT a.ACCOUNT_DATE as qjFROM RC_TRADEFLOW_DETAILS_NAPR a WHERE 1=1AND a.OURS_ACCOUNT_NUMBER LIKE '%02222'AND a.CUSTOMER_ACCOUNT_NAME_ORIGIN = s.CUSTOMER_ACCOUNT_NAME_ORIGINAND TRUNC(a.TRADE_DATE_BAK) = s.ts_dateAND a.TRADE_AMOUNT = s.TRADE_AMOUNTAND a.TRADE_STATUS = '成功');UPDATE RC_FUNDFLOW_DETAILS_NAPR A SET a.JOURNAL_VOUCHER_NUMBER = a_vouchers,a.ACCOUNT_DATE = a_periods,a.RULER_NAME_TRADEFLOW = '失敗和成功同一批次，失敗已經和資金表的資金流水編號匹配,將成功的憑證號拼接帶入那個資金'WHERE 1=1AND a.OURS_ACCOUNT_NUMBER LIKE '%02222'AND a.FUNDFLOW_DETAILS_ID IN (SELECT DISTINCT b.FUNDFLOW_DETAILS_ID FROM RC_TRADEFLOW_DETAILS_NAPR b WHERE 1=1AND b.OURS_ACCOUNT_NUMBER LIKE '%02222'AND b.CUSTOMER_ACCOUNT_NAME_ORIGIN = s.CUSTOMER_ACCOUNT_NAME_ORIGINAND TRUNC(b.TRADE_DATE_BAK) = s.ts_dateAND b.TRADE_AMOUNT = s.TRADE_AMOUNTAND b.TRADE_STATUS = '失敗');END LOOP;COMMIT;
END;
場景2完全性質的一對一匹配
現有一批數據，企業表和交易表已經匹配大量數據，但還有剩余，剩余數據匹配規則如下：
1.兩批數據必須在賬戶，戶名，日期，金額分組下僅有一條，防止匹配錯亂，
2.按照賬戶，戶名，日期，金額進行匹配
3.將企業表的憑證號和期間帶入資金。
使用窗口函數，在不影響性能的情況下，快速判斷該數據在該分區下該條數據是否僅有一條
BEGINFOR s in (
SELECTqy.DOC_SEQUENCE_VALUE,qy.PERIOD_NAME,zj.URIDfrom(SELECTls.mydate,ls.CUSTOMER_NAME,ls.money,ls.DOC_SEQUENCE_VALUE,ls.PERIOD_NAMEFROM(SELECTTO_CHAR(DEFAULT_EFFECTIVE_DATE, 'YYYY-MM-dd') AS mydate,CUSTOMER_NAME,(CASE WHEN ENTERED_DR <> 0 THEN ENTERED_DR ELSE -ENTERED_CR end) AS money,DOC_SEQUENCE_VALUE,PERIOD_NAME,count(*) OVER(PARTITION BY TO_CHAR(DEFAULT_EFFECTIVE_DATE, 'YYYY-MM-dd'),CUSTOMER_NAME,ENTERED_DR,ENTERED_CR) AS partition_countFROM rc_fin_transactions WHERE 1=1 AND BANKACCNO_SID = (select sid from RC_BANKACCDEF where BANKACCNO like '%11111'))lsWHERE ls.partition_count = 1) qyINNER JOIN (SELECTls.mydate,ls.DF_ACCOUNTNAME,ls.AMOUNT,ls.URIDFROM(SELECTTO_CHAR(POSTDATETIME, 'YYYY-MM-dd') AS mydate,DF_ACCOUNTNAME,AMOUNT,URID,COUNT(*) OVER (PARTITION BY TO_CHAR(POSTDATETIME, 'YYYY-MM-dd'), DF_ACCOUNTNAME, AMOUNT) AS partition_countFROM ISSUE202412_RECON_ZJLSBWHERE wf_account LIKE '%11111' AND 企業賬憑證號 IS NULL) lsWHERE ls.partition_count = 1) zjon qy.mydate = TO_CHAR(TO_DATE(zj.mydate,'YYYY-MM-DD')+1,'YYYY-MM-DD') AND qy.CUSTOMER_NAME = zj.DF_ACCOUNTNAME AND qy.money = zj.amount) LOOP
-- 將企業的憑證號和期間帶入資金
update ISSUE202412_RECON_ZJLSB
set 企業賬憑證號 = s.DOC_SEQUENCE_VALUE,企業賬期間 = s.PERIOD_NAME,規則名稱 = '日期+戶名+單筆金額1對1企業直接帶入資金'
where URID = s.URID;
END LOOP;
COMMIT;
END;
場景3虛擬戶的特殊匹配
現有交易表和資金表數據，要求交易表和資金表按照日期（天）匹配，
規則：
1.如果交易表當天的所有數據能夠資金表當天的數據按照日期（天）匹配，那就按當天匹配
2.如果資金表中當天不存在數據，那就找到比當前交易表日期小的資金表中存在的最大的日期進行匹配
3.如果資金表中既不存在當天數據，也不存在比當前交易表日期小的資金表中存在的最大的日期
那就找到比當前交易表日期大的資金表中存在的最小日期進行匹配。
總結：就近匹配
將與交易表當天匹配的資金表的那天的所有FUNDSFLOWCODE進行拼接，放入交易表當天的每一條數據中。
將日期匹配條件進行優化，
原先TRUNC(TRADE_DATE_BAK) = rec.TRADE_DATE_BAK 因為牽涉到函數計算字段，導致索引失效，
優化為范圍查詢，大大提升性能
AND d.postdatetime >= rec.TRADE_DATE_BAK
AND d.postdatetime < rec.TRADE_DATE_BAK + 1
優化原理，TRUNC會截取到日期的天如2025-01-03 23:33:13.111會被截取為2025-01-03 00:00:00.000
，故那天的所有日期都會被截取為2025-01-03 00:00:00.000，如果想獲取當天的所有日期，僅需找到當天日期范圍在
[2025-01-03 00:00:00.000,2025-01-04 00:00:00.000)也就是
AND d.postdatetime >= rec.TRADE_DATE_BAK
AND d.postdatetime < rec.TRADE_DATE_BAK + 1
DECLARECURSOR cur_data ISSELECT DISTINCT TRUNC(TRADE_DATE_BAK) AS TRADE_DATE_BAKFROM rc_tradeflow_details_napr -- 交易流水表WHERE OURS_ACCOUNT_NUMBER = '11111.com' AND ATTRIBUTE4 IS NULLORDER BY TRUNC(TRADE_DATE_BAK); cx_date DATE; SUM_FUNDSFLOWCODE VARCHAR2(4000);temp VARCHAR2(4000); commit_counter NUMBER := 0;
BEGINFOR rec IN cur_data LOOPBEGIN -- 找出與當前日期相同的日期SELECT TRUNC(d.postdatetime) INTO cx_dateFROM rc_fundflow_details_napr d -- 資金流水表WHERE 1=1AND d.MONEY_WAY ='充值'AND d.OURS_ACCOUNT_NUMBER in ('22222','33333','44444')AND d.CUSTOMER_ACCOUNT_NAME_ORIGIN = '支付寶（中國）網絡技術有限公司'AND d.CUSTOMER_ACCOUNT_NUMBER = '88888'-- AND TRUNC(TRADE_DATE_BAK) = rec.TRADE_DATE_BAK AND d.postdatetime >= rec.TRADE_DATE_BAK -- 優化為范圍查詢AND d.postdatetime < rec.TRADE_DATE_BAK + 1AND ROWNUM = 1;EXCEPTIONWHEN NO_DATA_FOUND THEN cx_date := NULL;END;-- 找出比當前日期小的最大日期IF cx_date IS NULL THENSELECT MAX(TRUNC(d.postdatetime)) INTO cx_dateFROM rc_fundflow_details_napr d -- 資金流水表WHERE 1=1AND d.MONEY_WAY ='充值'AND d.OURS_ACCOUNT_NUMBER in ('22222','33333','44444')AND d.CUSTOMER_ACCOUNT_NAME_ORIGIN = '支付寶（中國）網絡技術有限公司'AND d.CUSTOMER_ACCOUNT_NUMBER = '88888'AND d.postdatetime < rec.TRADE_DATE_BAK + INTERVAL '1' DAY;END IF;-- 找出比當前日期大的最小日期IF cx_date IS NULL THEN SELECT MIN(TRUNC(d.postdatetime)) INTO cx_dateFROM rc_fundflow_details_napr d -- 資金流水表WHERE 1=1AND d.MONEY_WAY ='充值'AND d.OURS_ACCOUNT_NUMBER in ('22222','33333','44444')AND d.CUSTOMER_ACCOUNT_NAME_ORIGIN = '支付寶（中國）網絡技術有限公司'AND d.CUSTOMER_ACCOUNT_NUMBER = '88888'AND d.postdatetime > rec.TRADE_DATE_BAK;END IF;-- 拼接這些日期的交易流水編號SELECT (RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, FUNDSFLOWCODE, ';') ORDER BY FUNDSFLOWCODE),'/E/text()').GETCLOBVAL(),';')) INTO SUM_FUNDSFLOWCODEFROM(SELECTFUNDFLOW_DETAILS_ID AS FUNDSFLOWCODEFROM rc_fundflow_details_napr d -- 資金流水表WHERE 1=1AND d.MONEY_WAY ='充值'AND d.OURS_ACCOUNT_NUMBER in ('22222','33333','44444')AND d.CUSTOMER_ACCOUNT_NAME_ORIGIN = '支付寶（中國）網絡技術有限公司'AND d.CUSTOMER_ACCOUNT_NUMBER = '88888'AND d.postdatetime >= cx_date AND d.postdatetime < cx_date +1);-- 更新交易流水表的本天的所有數據UPDATE rc_tradeflow_details_naprSET ATTRIBUTE4 = SUM_FUNDSFLOWCODEWHERE 1=1AND OURS_ACCOUNT_NUMBER = '11111.com' AND ATTRIBUTE4 IS NULLAND TRADE_DATE_BAK >= rec.TRADE_DATE_BAK AND TRADE_DATE_BAK < rec.TRADE_DATE_BAK+1;-- 增加計數器commit_counter := commit_counter + 1;-- 每處理一定數量的記錄后提交一次事務IF commit_counter >= 50 THEN COMMIT;commit_counter := 0; -- 重置計數器END IF;END LOOP;COMMIT;
END;場景4多對多匹配
現有一批數據，要求交易表按照賬戶，日期進行分組匯總得到匯總金額
資金表按照戶名，日期進行分組得到匯總金額
按照日期，賬戶，匯總金額進行匹配，將交易表該組的所有憑證號和期間拼接帶入資金表該組的每一條數據
將資金表該組的所有URID拼接帶入交易表該組的每一條數據。完成所有這樣的組的匹配。
DECLARECURSOR data_cur IS(SELECTtransaction_date AS mats_1FROM(SELECTa.BUSI_DATE AS transaction_date,sum(CASE WHEN a.ENTERED_DR <> 0 THEN a.ENTERED_DR ELSE -a.ENTERED_CR end) AS jy_moneyFROM rc_bank_transactions aWHEREa.bankaccno_sid = '111111111111'GROUP BY a.BUSI_DATE) jyINNER JOIN (SELECTtrunc(b.postdatetime) AS postdatetime,sum(b.amount) AS zj_moneyFROM issue202412_recon_zjlsb bWHEREb.wf_account ='111111111111'AND b.企業賬憑證號 IS NULLGROUP BY trunc(b.postdatetime)) zjON TRUNC(jy.transaction_date) = zj.postdatetime AND jy.jy_money = zj.zj_money);mats_1 date;a_vouchers varchar2(4000);a_periods varchar2(4000);b_urids varchar2(4000);
BEGINFOR res IN data_cur LOOPmats_1 := res.mats_1;SELECT EXTRACT(XMLAGG(XMLELEMENT(e, pz, ';') ORDER BY pz),'/E/text()').GETCLOBVAL() INTO a_vouchersFROM (SELECT DISTINCT a.DOC_SEQUENCE_VALUE as pzFROM rc_bank_transactions a WHERE TRUNC(a.BUSI_DATE) = mats_1 AND a.bankaccno_sid = '111111111111');SELECT EXTRACT(XMLAGG(XMLELEMENT(e, qj, ';') ORDER BY qj),'/E/text()').GETCLOBVAL() INTO a_periodsFROM (SELECT DISTINCT a.FIN_PERIOD_NAME as qjFROM rc_bank_transactions a WHERE TRUNC(a.BUSI_DATE) = mats_1AND a.bankaccno_sid = '111111111111'); SELECT EXTRACT(XMLAGG(XMLELEMENT(e,urid, ';') ORDER BY urid),'/E/text()').GETCLOBVAL() INTO b_uridsFROM (SELECT DISTINCT b.urid as uridFROM issue202412_recon_zjlsb bWHERE trunc(b.postdatetime) = mats_1 AND b.wf_account = '111111111111'); UPDATE rc_bank_transactions a SET a.urid = b_uridsWHERE TRUNC(a.BUSI_DATE) = mats_1AND a.bankaccno_sid = '111111111111';UPDATE issue202412_recon_zjlsb b SET b.企業賬憑證號 = a_vouchers,b.企業賬期間 = a_periodsWHERE trunc(b.postdatetime) = mats_1 AND b.wf_account = '111111111111';END LOOP;COMMIT;
END;
場景5一對一匹配
現有一批數據，企業表和交易表要求按照賬戶，投保單，金額，日期匹配，將企業表的憑證號和期間帶入交易表。
DECLARE
CURSOR c_update IS SELECT a.ROWID AS row_id, b.DOC_SEQUENCE_VALUE, b.PERIOD_NAME FROM rc_bank_transactions_202501 a,rc_fin_transactions_202501 b WHERE 1=1AND a.bankaccno_sid = b.bankaccno_sid AND a.REF_NUMBER = b.REF_NUMBERAND (CASE WHEN a.ENTERED_DR <> 0 THEN a.ENTERED_DR ELSE -a.ENTERED_CR end) = (CASE WHEN b.ENTERED_DR <> 0 THEN b.ENTERED_DR ELSE -b.ENTERED_CR end) AND TO_CHAR(a.BUSI_DATE, 'YYYY-MM') = TO_CHAR(b.DEFAULT_EFFECTIVE_DATE, 'YYYY-MM')AND a.DOC_SEQUENCE_VALUE IS NULL;
BEGIN FOR rec IN c_update LOOP UPDATE rc_bank_transactions_202501 SET DOC_SEQUENCE_VALUE = rec.DOC_SEQUENCE_VALUE, FIN_PERIOD_NAME = rec.PERIOD_NAME WHERE ROWID = rec.row_id; END LOOP; COMMIT;
END;
場景6 一對多匹配
現有一批數據要求交易數據按照賬戶，批次號，日期進行分組匯總得到匯總金額與資金表的賬戶，日期，金額進行匹配，
要求將交易表的拼接后的憑證號和期間帶入與之匹配的資金表的數據中，同時將資金表的FUNDSFLOWCODE帶入與之匹配的所以交易表的數據。
beginFOR s in (SELECTll.reqbatchno,ll.transaction_date,ss.FUNDSFLOWCODEFROM(SELECTa.reqbatchno,a.PAYSENTDATE AS transaction_date,sum(a.PAYMONEY) AS jyamountFROMissue202501_rc_batch_jylsb aWHEREa.wf_ACCOUNT = '1111111111111111111111'GROUP BYa.reqbatchno,a.PAYSENTDATE) llINNER JOIN (SELECTd.postdatetime AS postdatetime,d.amount,d.FUNDSFLOWCODEFROMissue202501_recon_zjlsb dWHEREd.wf_account = '1111111111111111111111') ss ON TRUNC(ss.postdatetime) = TRUNC(ll.transaction_date) AND ss.amount = ll.jyamount) LOOPupdate issue202501_rc_batch_jylsb
set URID = s.FUNDSFLOWCODE,資金表規則名稱 = '11111更新規則1：我方賬號+交易日期+單筆金額'
where 1=1
AND wf_ACCOUNT = '1111111111111111111111'
AND reqbatchno = s.reqbatchno
AND PAYSENTDATE = s.transaction_date
;UPDATE issue202501_recon_zjlsb e
SET e.規則名稱= '11111更新規則1：批次號+交易提交日期+匯總金額',e.企業賬憑證號 = (SELECT RTRIM(XMLAGG(XMLELEMENT(x, DOC_SEQUENCE_VALUE, ';').EXTRACT('//text()') ORDER BY DOC_SEQUENCE_VALUE).GetClobVal(), ';')FROM (SELECT DISTINCT DOC_SEQUENCE_VALUEFROM issue202501_rc_batch_jylsbWHERE URID = s.FUNDSFLOWCODE) sub),e.企業賬期間 = (SELECT RTRIM(XMLAGG(XMLELEMENT(x,FIN_PERIOD_NAME, ';').EXTRACT('//text()') ORDER BY FIN_PERIOD_NAME).GetClobVal(), ';')FROM(SELECT DISTINCT FIN_PERIOD_NAMEFROM issue202501_rc_batch_jylsbWHERE URID = s.FUNDSFLOWCODE) sua)
WHERE e.FUNDSFLOWCODE = s.FUNDSFLOWCODE;end loop;commit; -- 提交所有數據
end;
