hadoop - Hive 中的多个 Where 子查询不起作用

Question

我有如下查询:

SELECT T.MTH_END_DT, T.SRC_SYS_CD, T.BTCH_ID
FROM PROD_RCRR.BAL_CNTRL_LOG T
WHERE T.SRC_SYS_CD='SL'
AND T.MTH_END_DT in (SELECT(MAX(MTH_END_DT)) FROM PROD_RCRR.BAL_CNTRL_LOG)
AND T.BTCH_ID in (SELECT(MAX(BTCH_ID )) FROM PROD_RCRR.BAL_CNTRL_LOG)

一条错误消息显示 Hive 只能支持一个“in”子句。谁能给我一个解决方案？

Answer 1

你可以用 Join ON 子句替换整个东西

SELECT
    T.MTH_END_DT
  , T.SRC_SYS_CD
  , T.BTCH_ID
FROM PROD_RCRR.BAL_CNTRL_LOG T
JOIN ( SELECT
          MAX(MTH_END_DT) ENDT
        , MAX(BTCH_ID )   BTCH
       FROM PROD_RCRR.BAL_CNTRL_LOG ) X
  ON  T.SRC_SYS_CD='SL'
  AND T.MTH_END_DT = X.ENDT
  AND T.BTCH_ID = X.BTCH