hive脚本中AND EXISTS改为左连接left join

Question

    UNION ALL
    SELECT A.*,
            ERROR_TWO_TYPE
      FROM SRBZ_HIVE.TMP_YWJH_ZQZRR_DW_02 A
     WHERE A.PRODUCT_ID IN (2020966, 2100297, 2021030)
       AND EXISTS
     (SELECT 1
              FROM IWC_DWD.DWD_CRM_CUST_DAY B
             WHERE TRIM(A.USE_CUST_ID) = TRIM(B.CUST_ID)
               AND B.CERTI_TYPE NOT IN
                   ('1', '41', '12', '3', '2', '14', '4', '42')
               AND B.STATUS_CD = '1000'
               AND P_DAY_ID = '${date_no}')

大家帮忙看看

Answer 1

SELECT A.*,
                ERROR_TWO_TYPE
    FROM SRBZ_HIVE.TMP_YWJH_ZQZRR_DW_02 A
    left join 
    (SELECT TRIM(B.CUST_ID) cid
                    FROM IWC_DWD.DWD_CRM_CUST_DAY B
                 WHERE B.CERTI_TYPE NOT IN
                             ('1', '41', '12', '3', '2', '14', '4', '42')
                     AND B.STATUS_CD = '1000'
                     AND P_DAY_ID = '${date_no}') B1
 ON TRIM(A.USE_CUST_ID)=B1.cid
 where A.PRODUCT_ID IN (2020966, 2100297, 2021030) and B1.cid is not null