weixin_44095378 2022-04-28 23:51 采纳率: 100%
浏览 42
已结题

关于hive上NOT IN改为左连接left join

DROP TABLE IF EXISTS TMP_YWJH_YKDZ_04;
CREATE TABLE TMP_YWJH_YKDZ_04 AS
SELECT A.*, B.LNX_NUM LNX_NUM
FROM SRBZ_HIVE.TMP_YWJH_YKDZ_02 A, SRBZ_HIVE.TMP_YWJH_YKDZ_03 B
WHERE A.CERTI_NUMBER = B.CERTI_NUMBER
AND A.CERTI_NUMBER NOT IN (SELECT CERTI_NUMBER FROM SRBZ_HIVE.TMP_YWJH_YKDZ_02 WHERE create_date>to_date(from_u)

求帮忙看看 如何改成left join

  • 写回答

1条回答 默认 最新

  • bj_0163_bj 2022-04-29 08:33
    关注

    DROP TABLE IF EXISTS TMP_YWJH_YKDZ_04;
    CREATE TABLE TMP_YWJH_YKDZ_04 AS
    SELECT A.*, B.LNX_NUM LNX_NUM
    FROM SRBZ_HIVE.TMP_YWJH_YKDZ_02 A
    join SRBZ_HIVE.TMP_YWJH_YKDZ_03 B
    on A.CERTI_NUMBER = B.CERTI_NUMBER
    left join (SELECT CERTI_NUMBER FROM SRBZ_HIVE.TMP_YWJH_YKDZ_02 WHERE create_date>to_date(from_u)) C
    on A.CERTI_NUMBER=C.CERTI_NUMBER
    WHERE C.CERTI_NUMBER is null

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

问题事件

  • 系统已结题 5月7日
  • 已采纳回答 4月29日
  • 创建了问题 4月28日