sql - 在 Hive 中带有子查询 IN/EXIST 的左外连接

Question

全部，所以我正在尝试运行查询。该查询由 7 个表组成，我希望所有表都基于 A.conn_keyy 和其他带有子句“ON”的表保持连接

当我想加入基于 A 的 CPLCUR 时，我感到困惑，但不起作用。

(CPLCUR.conn_keyy in ( a.conn_keyy = b.conn_keyy )

出现错误：join 'conn_key' 中遇到左右别名

set hive.support.quoted.identifiers=none;
select 
coalesce(a.conn_keyy, b.conn_keyy,CPLCUR.conn_keyy) as rrconn_keyy,
b.rfbbn, b.LINES_ID,b.TYPE,CPLCUR.*

FROM
(tablee.aa)A

LEFT OUTER JOIN 
(tablee.bb) B 
ON (A.conn_keyy = B.conn_keyy)

LEFT OUTER JOIN (SELECT `(c21)?+.+` FROM tablee.cc ) CPLCUR
ON (CPLCUR.conn_keyy in ( a.conn_keyy = b.conn_keyy )
AND CPLCUR.cllt = REGEXP_EXTRACT(B.rfbbn,'^(?:[^*]*\\*){2}([^*]*)',1))

LEFT OUTER JOIN (SELECT DISTINCT * FROM tablee.dd) CPLBAL
ON CPLBAL.conn_keyy = A.conn_keyy
AND CPLBAL.SEQUENCE = CPLCUR.SEQUENCE
AND CPLBAL.dtdt = '1999' 

LEFT OUTER JOIN
 (tablee.REP)REP 
ON REP.relino = B.lnido

LEFT OUTER JOIN tablee.P PRD
ON PRD.PRODUCT_CODE = REGEXP_EXTRACT(A.conn_keyy,'[.]([^.]+)',1)
AND PRD.dtdt = '1999'

WHERE B.lnido LIKE 'PLCONS1%'
) rrvv;

得到这个的最佳做法是什么？

The desired results:
+-----------+---------+--------+----------+-------------+-------+-----+-----+
| conn_keyy | b.rfbbn | b.LINES| b.TYPE   |   CPLCUR    | CPLBAL| REP | PRD |
+-----------+---------+--------+----------+-------------+-------+-----+-----+
| 111       | aaa     | PCOS1% | bbsr     | 2019-02-21  |       |     |     |
| 200       |         | PCOS1% | ny       | X           |       |     |     |
| 222       | bbb     | PCOS1% | pp       | Y           |       |     |     |
| 300       | rrr     | PCOS1% | atl      | 2019-03-18  |       |     |     |
| 333       | ccc     | PCOS1% | dd       | Z           |       |     |     |
| 400       | vvv     | PCOS1% | tt       | 2019-03-18  |       |     |     |
+-----------+---------+--------+----------+-------------+-------+-----+-----+

标签： sqlleft-joinhiveql