一个复杂关联的sql
在项目中遇到了一个比较复杂关系的sql,关联关系有些模糊,现在梳理一下
sql如下:
SELECT TRAN.TRANS_DATE, TRAN.TRANS_TIME, TRAN.BUSI_TRAC_CODE YW, PZK.JSPZK_PZBH PZBH, DECODE(PZK.JSPZK_PZRQ, '20181224', DECODE(KMZD.F_YEFX, 1, RYE.JSZHRYEB_QCYE, 2, RYE.JSZHRYEB_QCYE * (-1), 0), DECODE(KMZD.F_YEFX, 1, YE.JSZHYEB_QCYE, 2, YE.JSZHYEB_QCYE * (-1), 0)) QCYE, DECODE(PZFL.JSPZFL_JZFX, '1', PZFL.JSPZFL_JE, null) JFFS, DECODE(PZFL.JSPZFL_JZFX, '2', PZFL.JSPZFL_JE, null) DFFS, TRAN.AMOUNT, DECODE(KMZD.F_YEFX, 2, PZFL.JSPZFL_BAL * (-1) + NVL(PZFL.JSPZFL_FUNDSPOOL_BAL, 0) * (-1), PZFL.JSPZFL_BAL + NVL(PZFL.JSPZFL_FUNDSPOOL_BAL, 0)) ZHDQYE , DECODE(PZFL.JSPZFL_OPP_NAME, NULL, TRAN.OPP_CUST_ACC_NAME, PZFL.JSPZFL_OPP_NAME) OPPNAME , DECODE(PZFL.JSPZFL_OPP_ACTNO, NULL, TRAN.OPP_CUST_ACC_CODE, PZFL.JSPZFL_OPP_ACTNO) OPPACTNO, PZK.JSPZK_BZ ZY FROM JSPZFL PZFL, TRAN_FLOW TRAN, LSKMZD KMZD, JSPZK PZK left join JSZHYEB YE on YE.JSZHYEB_BH = '990110100187704203' AND YE.JSZHYEB_RQ = PZK.JSPZK_PZRQ left join JSZHRYEB RYE on RYE.JSZHRYEB_BH = '990110100187704203' WHERE PZK.JSPZK_PZNM = PZFL.JSPZFL_PZNM AND TRAN.VOUCHER_ID = PZK.JSPZK_PZNM AND TRAN.BUSI_TRAC_CODE NOT IN ('IFP_ZJXB', 'IFP_ZJSH') AND PZFL.JSPZFL_KMBH = KMZD.F_KMBH AND PZK.JSPZK_JZBZ <> '3' AND NVL(PZFL.JSPZFL_BY7, '0') != '1' AND TRAN.CUST_CODE IN (select cust_code from CUST_CORP_INFO start with cust_code = '99101001877' connect by prior organ_id = parent_code AND CUST_TYPE = '5') AND EXISTS (SELECT 1 FROM V_DIC_ACC ACC WHERE ACC.BH = PZFL.JSPZFL_ZHBH AND ACC.BH = '990110100187704203') AND PZK.JSPZK_PZRQ >= '20181224' AND PZK.JSPZK_PZRQ <= '20181224' ORDER BY PZK.JSPZK_PZNM, PZFL.JSPZFL_FLNM, TRAN.TRAC_ID