sql - HIVE ERROR : I am getting EOF error at 1, 对于第一个 LEFT OUTER JOIN 的 ON 子句之后的 WHERE 子句，对于配置单元中的以下代码

Question

select *
from table1 a

LEFT OUTER JOIN 
(
   select * 
   from table99
   where col = 1
)b

ON (a.col1 = b.col1)

WHERE a.col2 = b.col2 AND SIGN(a.col3) = 1


LEFT OUTER JOIN
(
   select *
   from table99
   where col = 2
)c

ON (a.col1 = c.col1)

WHERE a.col2 = c.col2 AND SIGN(a.col3) = 1;

Answer 1

正确形成的 SQL 查询只有一个 where 子句(不包括 CTE 和子查询)。所以:

select *
from table1 a LEFT OUTER JOIN 
     (select * 
      from table99
      where col = 1
     ) b
     ON a.col1 = b.col1 AND
        a.col2 = b.col2 LEFT OUTER JOIN
     (select *
      from table99
      where col = 2
     ) c
     ON a.col1 = c.col1 AND
        a.col2 = c.col2
WHERE SIGN(a.col3) = 1;

但是，这似乎太复杂了。这个怎么样？

select *
from table1 a LEFT OUTER JOIN 
     table99 b
     ON a.col1 = b.col1 AND
        a.col2 = b.col2 AND
        b.col = 1 LEFT OUTER JOIN
     table99 c
     ON a.col1 = c.col1 AND
        a.col2 = c.col2 AND
        c.col = 2 
WHERE a.col3 > 0;