时间戳

首页 > 解决方案 > Hive Exists 条件不适用于多种条件

sql - Hive Exists 条件不适用于多种条件

问题描述

我正在运行以下查询

select * from Data t1 
where not exists (select 1 from data2 t2 where
t1.diagonosiscode2 = t2.dx and 
t1.diagonosiscode3 = t2.dx and
t1.diagonosiscode4 = t2.dx and
t1.diagonosiscode5 = t2.dx and
t1.diagonosiscode6 = t2.dx and
t1.diagonosiscode7 = t2.dx and
t1.diagonosiscode8 = t2.dx and
t1.diagonosiscode9 = t2.dx and
t1.diagonosiscode10 = t2.dx and
t1.diagonosiscode11 = t2.dx and
t1.diagonosiscode12 = t2.dx and
t1.diagonosiscode13 = t2.dx and
t1.diagonosiscode14 = t2.dx and
t1.diagonosiscode15 = t2.dx and
t1.diagonosiscode16 = t2.dx and
t1.diagonosiscode17 = t2.dx and
t1.diagonosiscode18 = t2.dx and
t1.diagonosiscode19 = t2.dx and
t1.diagonosiscode20 = t2.dx and
t1.diagonosiscode21 = t2.dx and
t1.diagonosiscode22 = t2.dx and
t1.diagonosiscode23 = t2.dx and
t1.diagonosiscode24 = t2.dx and
t1.diagonosiscode25 = t2.dx )

上述查询引发以下错误。

org.apache.hive.service.cli.HiveSQLException：编译语句时出错：FAILED：SemanticException [错误 10250]：第 2:33 行无效的子查询表达式“dx”：对于存在/不存在运算符，子查询必须是相关的。

但是当我为单个条件编写时执行相同的查询

select * from Data t1 
where not exists (select 1 from  Data2 t2
                  where t1.diagnosiscode3 = t2.dx)

hive 是否只支持一个存在条件。

由于 hive 不支持，我也无法在条件下执行多个。

如何解决问题。

标签： sqlhive

解决方案

尝试使用left join：

select t1.*
from Data t1 left join
     data2 t2 
     on t1.diagonosiscode2 = t2.dx and 
        t1.diagonosiscode3 = t2.dx and
        . . .
where t2.dx is null;

我注意到您正在将所有代码与dx. 所以你也可以这样做：

select t1.*
from Data t1 
where t1.diagonosiscode2 = t1.diagonosiscode3 and 
      t1.diagonosiscode2 = t1.diagonosiscode4 and
      . . .
     not exists (select 1
                 from data2 t2
                 where t1.diagonosiscode4 = t2.dx 
                );

推荐阅读