ORACLE NOT EXISTS不等值 改写成hive

 

 

 

 

 

 

 

 

 

ORACLE NOT EXISTS不等值 改写成hive

 

  SELECT A.A1, A.A2,A.A3,A.A4
  FROM A
  LEFT JOIN C 
  ON A.A6= C.C6
 WHERE NOT EXISTS (SELECT 1
          FROM B
         WHERE B.B1 = A.A1
           AND B.B2 = A.A2
           AND ((A.A3 < B.B3) OR (A.A3 = B.B3 AND A.A4 <B.B4)

 

下面存在oracle not exists 的不等值关联改写成hive 如下

---修改成hive语句。hive 不存在not exists ，也不存在not exists存在子查询
-- hive 不能left join 不等值关联，只能放在where 中impala 可以

---修改成hive语句。hive 不存在not exists ，也不存在not exists存在子查询
-- hive 不能left join 不等值关联，只能放在where 中impala 可以
SELECT A.*
FROM
 (SELECT DISTINCT 
 A.A1,A.A2,A.A3,A.A4,A.A5
   FROM A
  LEFT JOIN C 
  ON A.A6= C.C6
 LEFT JOIN (SELECT B1,B2,B3,B4,B5
              FROM B)
            ON B.B1 = A.A1
           AND B.B2 = A.A2)F
 LEFT JOIN 

 (SELECT  
 A.A1,A.A2,A.A3,A.A4,A.A5
   FROM A
  LEFT JOIN C 
  ON A.A6= C.C6
 LEFT JOIN (SELECT B1,B2,B3,B4,B5
              FROM B)
            ON B.B1 = A.A1
           AND B.B2 = A.A2
         WHERE ((A.A3 < B.B3) OR (A.A3 = B.B3 AND A.A4 <B.B4)           
           )F2
 ON F1.A1 = F2.A1
 AND F1.A2= F2.A3
 WHERE F2.B1 IS NULL