hive脚本把exists或not exist改成inner join或left join

Question

    SELECT T.*,
           '个人客户' ERROR_TWO_TYPE
      FROM (SELECT *
              FROM SRBZ_HIVE.TMP_YWJH_ZQZRR_GR_02 A
             WHERE EXISTS (SELECT *
                      FROM (SELECT CERTI_NUMBER,
                                   COUNT(DISTINCT B.CUST_NAME)
                              FROM IWC_DWD.DWD_CRM_CUST_DAY B
                             WHERE B.STATUS_CD = '1000'
                               AND B.P_DAY_ID = '${date_no}'
                             GROUP BY B.CERTI_NUMBER
                            HAVING COUNT(DISTINCT B.CUST_NAME) > 1) C
                     WHERE C.CERTI_NUMBER = A.CERTI_NUMBER)) T
     WHERE NOT EXISTS (SELECT *
              FROM SRBZ_HIVE.C_IDCARD_VERIFY C
             WHERE T.CERTI_NUMBER = C.IDCARD_NO
               AND T.CUST_NAME = C.IDCARD_NAME)
       AND NOT EXISTS
     (SELECT *
              FROM SRBZ_HIVE.IDENTIFY_CARD_INFO TT
             WHERE T.CERTI_NUMBER = TT.IDENTIFY_CARD_ID
               AND T.CUST_NAME = TT.NAME)
       AND (T.REAL_FLAG <> 'T' OR T.REAL_FLAG IS NULL)

Answer 1

SELECT T.*,
       '个人客户' ERROR_TWO_TYPE
FROM (SELECT *
          FROM SRBZ_HIVE.TMP_YWJH_ZQZRR_GR_02 A
          inner join 
          (SELECT CERTI_NUMBER,
                               COUNT(DISTINCT B.CUST_NAME)
                          FROM IWC_DWD.DWD_CRM_CUST_DAY B
                         WHERE B.STATUS_CD = '1000'
                           AND B.P_DAY_ID = '${date_no}'
                         GROUP BY B.CERTI_NUMBER
                        HAVING COUNT(DISTINCT B.CUST_NAME) > 1) C
        on C.CERTI_NUMBER = A.CERTI_NUMBER) T
LEFT JOIN SRBZ_HIVE.C_IDCARD_VERIFY C1
ON T.CERTI_NUMBER = C.IDCARD_NO AND T.CUST_NAME = C.IDCARD_NAME
LEFT JOIN SRBZ_HIVE.IDENTIFY_CARD_INFO TT
ON T.CERTI_NUMBER = TT.IDENTIFY_CARD_ID AND T.CUST_NAME = TT.NAME
WHERE C1.IDCARD_NO IS NULL 
AND TT.IDENTIFY_CARD_ID IS NULL 
AND (T.REAL_FLAG <> 'T' OR T.REAL_FLAG IS NULL)