---笔记总结-----
select COD_CUST_ID,--ECIF客户号 CERT_TYPE,--ECIF证类型 CAST(CERT_NUM AS VARCHAR(50)) AS ECIF_CERT_NO, --ECIF证件号 MAX(BUSI_DT) over(partition by COD_CUST_ID) as BUSI_DT, row_number() over(partition by COD_CUST_ID order by BUSI_DT) as rowid from SDM.S_ECIF_ECIF_CERT_INFO where IS_FIST = 'Y' AND ETL_F = '1' and cod_cust_id='8000243882'
COD_CUST_ID CERT_TYPE ECIF_CERT_NO BUSI_DT ROWID
-------------- ------------ ---------------- ---------- --------
8000243882 201 522702NA000211X 20181216 1
8000243882 201 522702NA000211X 20181216 2
LISTAGG(sign_type,',') sign_type
--------------整数除以decimal的问题------------------
select a.dep ,a.num1 person_num --,a.dep*1.000/cast(a.num1 as decimal (20,2)) , a.dep*1.000/a.num1 person_avg from ( select ACC_ORG_CODE, sum(CURR_DEPOSIT_AMT)/100000000.0 dep ,cast(count(1)/10000.0 as decimal (20,2)) num1 from tda.T_QNJF_CUST_SAVING_DEP where biz_dt='20200407' group by ACC_ORG_CODE ) a
---DB2-----where--从右向左执行<<------------
----false-----
select DAT_IC_ISSUE, DAT_IC_EXPIRY ,EXTERNAL_CUSTOMER_IC from sdm.s_ofcr_ci_custmast a where length(DAT_IC_EXPIRY)=8 and DAT_IC_EXPIRY not in ('','长期','0') and TO_DATE(DAT_IC_EXPIRY,'yyyymmdd')<=ADD_MONTHS(TO_DATE('20200408','yyyymmdd'),1 ) and TO_DATE(DAT_IC_EXPIRY,'yyyymmdd')>=TO_DATE('20200408','yyyymmdd')
----true-----
select DAT_IC_ISSUE, DAT_IC_EXPIRY ,EXTERNAL_CUSTOMER_IC from sdm.s_ofcr_ci_custmast a where TO_DATE(DAT_IC_EXPIRY,'yyyymmdd')<=ADD_MONTHS(TO_DATE('20200408','yyyymmdd'),1 ) and TO_DATE(DAT_IC_EXPIRY,'yyyymmdd')>=TO_DATE('20200408','yyyymmdd') and length(DAT_IC_EXPIRY)=8 and DAT_IC_EXPIRY not in ('','长期','0')
----or----true--safe---
select DAT_IC_EXPIRY, TO_DATE(DAT_IC_EXPIRY,'yyyymmdd') ,EXTERNAL_CUSTOMER_IC from ( select DAT_IC_EXPIRY ,EXTERNAL_CUSTOMER_IC from sdm.s_ofcr_ci_custmast a where DAT_IC_EXPIRY not in ('','长期','0') and length(DAT_IC_EXPIRY)=8 ) a where --a.day1<'01' and a.day1>'31' --a.month1<'01' and a.month1>'12' TO_DATE(DAT_IC_EXPIRY,'yyyymmdd')<=ADD_MONTHS(TO_DATE('20200408','yyyymmdd'),1 ) and TO_DATE(DAT_IC_EXPIRY,'yyyymmdd')>=TO_DATE('20200408','yyyymmdd')
-------person-avg-dep----------------------
select ACC_ORG_CODE ,sum_per_nums ,curr ,apd ,curr*100.0/sum_per_nums||'%' curper ,apd*100.0/sum_per_nums||'%' apdper from ( select count(1) sum_per_nums ,ACC_ORG_CODE ,sum(case when CURR_DEPOSIT_AMT>100000 then 1 else 0 end) curr ,sum(case when APD_DEP_AMT>100000 then 1 else 0 end) apd from tda.T_QNJF_CUST_saving_dep group by ACC_ORG_CODE --where CURR_DEPOSIT_AMT>100000 ) A
----------full join --------两个关联条件------------------------
select a.id a_id,a.name a_name, b.id b_id, b.NAME b_name, case when a.id is null then b.id else a.id end z_id , case when a.name is null then b.name else a.name end z_name_old , case when a.id is null and a.name is null then b.name else a.name end z_name --只关联id时z_name_old和z_name取的值不一样，关联两个条件时，取的值是一样的 from ( select id,name from tmp.DZZ_T1 where id=26 ) a full join ( select id,name from tmp.DZZ_T2 where id=26 ) b on a.id=b.id and a.name=b.name order by a.id
------------full join------two join---------------
select case when A.id is null then b.id else A.id end sid ,case when A.tt is null then b.tt else A.tt end stt ,'' stt1 ,a.ID a_id ,b.id b_id ,a.tt a_tt ,b.tt b_tt from ( select id,'' tt from tmp.dzz_t1 ) a full join ( select id,'' tt from tmp.dzz_t2 ) b on a.id=b.id and a.tt=b.tt
----------sql-server--------------------maxdays------------
with t1 as (select distinct custno,datadt from cust_jy ) select custno,max(nums1) maxdays from ( select custno,nums,count(1) nums1 from ( select custno,c.days1-rowid nums from ( select a.custno,a.datadt ,row_number() over (partition by a.custno order by a.datadt) rowid ,datediff(day,'20200101',a.datadt) days1 from t1 A --where a.custno='1000001640' ) C ) D group by custno,nums ) E group by custno
---DB2-----
select custno,max(nums1) maxdays from ( select custno,tempdt, count(1) nums1 from ( select custno--,datadt,rowid ,convert(char(8),dateadd(day,-rowid,datadt),112) tempdt from ( select a.custno,a.datadt ,row_number() over (partition by a.custno order by a.datadt) rowid from (select distinct custno,datadt from cust_jy ) A --where a.custno='1000001640' ) C ) D group by custno,tempdt ) E group by custno
2021－7－21
