Hive-Where和OR子句错误 [英] Hive - Where and OR clause error

问题描述

我正在尝试在Hive中运行此查询，但收到错误10249(不支持的查询表达式-仅支持1个子查询...)

Hi I am trying to run this query in Hive, but get the error 10249 (Unsupported query expression - only 1 subquery is supported...)

select count(*) from
(
   select * from tableA
   union all
   select * from tableB
) a
where a.field1 in (select fieldA in tableC)
or a.field2 in (select fieldA in tableC)
or a.field3 in (select fieldA in tableC);

有人会知道我该怎么写，以便Hive支持此查询(在SQL Server中可以正常工作)

Would anybody know how I can write this so that Hive supports this query (works fine in SQL server)

推荐答案

在CTE中转换子查询，在where子句中左联接并使用or条件.

Covert you sub query in CTE , left join and use or condition in where clause.

IE.

   with temp as 
   (
   select * from tableA
   union all
   select * from tableB
   )

select COUNT(a.*)
       from temp a left join tableC a1 on  a.field1 =a1.fieldA
       left join tableC a2 on  a.field2 =a2.fieldA
       left join tableC a3 on  a.field3 =a3.fieldA
     where   a1.fieldA is not null 
          or a3.fieldA is not null
          or a3.fieldA is not null

这篇关于Hive-Where和OR子句错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！