工作中sql总结-CSDN博客

本文链接：https://blog.csdn.net/qq_29270805/article/details/147409110

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_AMOUNT
	   FROM RC_TRADEFLOW_DETAILS_NAPR a
	   WHERE 1=1
	   AND 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) >=1
	   AND
	   sum(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 LOOP
		
	
	 SELECT EXTRACT(XMLAGG(XMLELEMENT(e, pz, ';') ORDER BY pz),'/E/text()').GETCLOBVAL() INTO a_vouchers
		FROM (
				SELECT DISTINCT a.JOURNAL_VOUCHER_NUMBER  as pz
				FROM RC_TRADEFLOW_DETAILS_NAPR a 
				WHERE 1=1
				AND a.OURS_ACCOUNT_NUMBER  LIKE '%2222'
				AND a.CUSTOMER_ACCOUNT_NAME_ORIGIN = s.CUSTOMER_ACCOUNT_NAME_ORIGIN
				AND TRUNC(a.TRADE_DATE_BAK) = s.ts_date
				AND a.TRADE_AMOUNT = s.TRADE_AMOUNT
				AND a.TRADE_STATUS = '成功'
				);
		SELECT EXTRACT(XMLAGG(XMLELEMENT(e, qj, ';') ORDER BY qj),'/E/text()').GETCLOBVAL() INTO a_periods
		FROM (
				SELECT DISTINCT a.ACCOUNT_DATE  as qj
				FROM RC_TRADEFLOW_DETAILS_NAPR a 
				WHERE 1=1
				AND a.OURS_ACCOUNT_NUMBER  LIKE '%02222'
				AND a.CUSTOMER_ACCOUNT_NAME_ORIGIN = s.CUSTOMER_ACCOUNT_NAME_ORIGIN
				AND TRUNC(a.TRADE_DATE_BAK) = s.ts_date
				AND a.TRADE_AMOUNT = s.TRADE_AMOUNT
				AND 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=1
		AND 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=1
				AND b.OURS_ACCOUNT_NUMBER  LIKE '%02222'
				AND b.CUSTOMER_ACCOUNT_NAME_ORIGIN = s.CUSTOMER_ACCOUNT_NAME_ORIGIN
				AND TRUNC(b.TRADE_DATE_BAK) = s.ts_date
				AND b.TRADE_AMOUNT = s.TRADE_AMOUNT
				AND b.TRADE_STATUS = '失败'
		)
		;
		
	END LOOP;
	COMMIT;
END;

场景2完全性质的一对一匹配

现有一批数据，企业表和交易表已经匹配大量数据，但还有剩余，剩余数据匹配规则如下：
1.两批数据必须在账户，户名，日期，金额分组下仅有一条，防止匹配错乱，
2.按照账户，户名，日期，金额进行匹配
3.将企业表的凭证号和期间带入资金。
使用窗口函数，在不影响性能的情况下，快速判断该数据在该分区下该条数据是否仅有一条

BEGIN
    FOR s in (
SELECT
      qy.DOC_SEQUENCE_VALUE,qy.PERIOD_NAME,zj.URID
      from(
		      SELECT
		        ls.mydate,
		        ls.CUSTOMER_NAME,
		        ls.money,
		        ls.DOC_SEQUENCE_VALUE,
		        ls.PERIOD_NAME
		      FROM(SELECT
		                TO_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_count
		            FROM rc_fin_transactions 
		            WHERE 1=1 AND BANKACCNO_SID = (select sid from RC_BANKACCDEF  where BANKACCNO like '%11111'))ls
		      WHERE ls.partition_count = 1
        ) qy
        INNER JOIN (
                      SELECT
                            ls.mydate,
                            ls.DF_ACCOUNTNAME,
                            ls.AMOUNT,
                            ls.URID
                      FROM(SELECT
                                TO_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_count
                            FROM ISSUE202412_RECON_ZJLSB
                            WHERE wf_account LIKE '%11111' AND 企业账凭证号 IS NULL) ls
                      WHERE ls.partition_count = 1
        ) zj
        on 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

DECLARE
    CURSOR cur_data IS
        SELECT 
        DISTINCT TRUNC(TRADE_DATE_BAK)  AS TRADE_DATE_BAK
        FROM rc_tradeflow_details_napr -- 交易流水表
        WHERE OURS_ACCOUNT_NUMBER = '11111.com' 
		AND ATTRIBUTE4 IS NULL
		ORDER BY TRUNC(TRADE_DATE_BAK); 
		
    cx_date DATE;    
   	SUM_FUNDSFLOWCODE VARCHAR2(4000);
    temp VARCHAR2(4000); 
	commit_counter NUMBER := 0; 
BEGIN
    FOR rec IN cur_data LOOP
		
		BEGIN 
			-- 找出与当前日期相同的日期
			SELECT TRUNC(d.postdatetime) INTO cx_date
			FROM rc_fundflow_details_napr d -- 资金流水表
			WHERE 1=1
			AND 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 + 1
			AND ROWNUM = 1;
		EXCEPTION
            WHEN NO_DATA_FOUND THEN cx_date := NULL;
		END;
	
		
		-- 找出比当前日期小的最大日期
		IF cx_date IS NULL THEN
		
		SELECT MAX(TRUNC(d.postdatetime)) INTO cx_date
		FROM rc_fundflow_details_napr d -- 资金流水表
		WHERE 1=1
		AND 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_date
		FROM rc_fundflow_details_napr d -- 资金流水表
		WHERE 1=1
		AND 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_FUNDSFLOWCODE
		FROM(
		SELECT
			FUNDFLOW_DETAILS_ID AS FUNDSFLOWCODE
		FROM rc_fundflow_details_napr d -- 资金流水表
		WHERE 1=1
		AND 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_napr
        SET ATTRIBUTE4 = SUM_FUNDSFLOWCODE
        WHERE 1=1
        AND OURS_ACCOUNT_NUMBER = '11111.com' 
        AND ATTRIBUTE4 IS NULL
        AND 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拼接带入交易表该组的每一条数据。完成所有这样的组的匹配。

DECLARE
	CURSOR data_cur IS(
		SELECT
			transaction_date AS mats_1
		FROM
			(
				SELECT
				a.BUSI_DATE AS transaction_date,
				sum(CASE WHEN a.ENTERED_DR <> 0 THEN a.ENTERED_DR ELSE -a.ENTERED_CR end) AS jy_money
				FROM rc_bank_transactions a
				WHERE
					a.bankaccno_sid = '111111111111'
				GROUP BY a.BUSI_DATE
				) jy
		INNER JOIN (
				SELECT
					trunc(b.postdatetime) AS postdatetime,
					sum(b.amount) AS zj_money
				FROM issue202412_recon_zjlsb b
				WHERE
					b.wf_account ='111111111111'
					AND b.企业账凭证号 IS NULL
				GROUP BY trunc(b.postdatetime)
			) zj
		ON 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);
BEGIN
	FOR res IN data_cur LOOP
		
		mats_1 := res.mats_1;
		
    
		SELECT EXTRACT(XMLAGG(XMLELEMENT(e, pz, ';') ORDER BY pz),'/E/text()').GETCLOBVAL() INTO a_vouchers
		FROM (
				SELECT DISTINCT a.DOC_SEQUENCE_VALUE as pz
				FROM 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_periods
		FROM (
				SELECT DISTINCT a.FIN_PERIOD_NAME as qj
				FROM rc_bank_transactions a 
				WHERE TRUNC(a.BUSI_DATE) = mats_1
				AND a.bankaccno_sid = '111111111111'
				);	
		SELECT EXTRACT(XMLAGG(XMLELEMENT(e,urid, ';') ORDER BY urid),'/E/text()').GETCLOBVAL() INTO b_urids
		FROM (
				SELECT DISTINCT b.urid as urid
				FROM issue202412_recon_zjlsb b
				WHERE trunc(b.postdatetime) = mats_1 
				AND b.wf_account = '111111111111'
				);		
        UPDATE rc_bank_transactions a SET a.urid = b_urids
        WHERE TRUNC(a.BUSI_DATE) = mats_1
		AND a.bankaccno_sid = '111111111111';
	
		UPDATE issue202412_recon_zjlsb b 
		SET b.企业账凭证号 = a_vouchers,
			b.企业账期间 = a_periods
		WHERE 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=1
        AND a.bankaccno_sid = b.bankaccno_sid 
        AND a.REF_NUMBER = b.REF_NUMBER
        AND (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带入与之匹配的所以交易表的数据。

begin
  FOR s in (
				SELECT
					ll.reqbatchno,ll.transaction_date,ss.FUNDSFLOWCODE
				FROM
					(
					SELECT
						a.reqbatchno,
						a.PAYSENTDATE AS transaction_date,
						sum(a.PAYMONEY) AS jyamount
					FROM
						issue202501_rc_batch_jylsb a
					WHERE
						a.wf_ACCOUNT = '1111111111111111111111'
					GROUP BY
						a.reqbatchno,
						a.PAYSENTDATE) ll
				INNER JOIN (
					SELECT
						d.postdatetime AS postdatetime,
						d.amount,
						d.FUNDSFLOWCODE
					FROM
						issue202501_recon_zjlsb d
					WHERE
						d.wf_account = '1111111111111111111111') ss 
					ON TRUNC(ss.postdatetime) = TRUNC(ll.transaction_date) AND ss.amount = ll.jyamount
					
					) LOOP
						
						
  
update 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_VALUE
                 FROM issue202501_rc_batch_jylsb
                 WHERE 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_NAME
                FROM issue202501_rc_batch_jylsb
                WHERE URID = s.FUNDSFLOWCODE
            ) sua
    )
WHERE e.FUNDSFLOWCODE = s.FUNDSFLOWCODE;

  end loop;
  commit; -- 提交所有数据
end;