hive - 如何在配置单元中使用超过 1 个子查询

Question

我正在执行以下查询时出现错误。

失败:SemanticException [错误 10249]:第 13:15 行不支持的子查询表达式“master_cd”:仅支持 1 个子查询表达式。

SELECT
cfs.roll_no,
max(cclas.crdm_cd) as crdm_cd,
max(cclas.kjtm_cd) as kjtm_cd
FROM cust_focus cfs
LEFT JOIN cust_class cclas
ON (cfs.CF_CLAS_NO = cclas.CLAS_NO
AND cfs.DFS_CD = cclas.DFS_CD 
AND cclas.D_AREA = 'US' 
AND cclas.active_flag = 'Y')
WHERE cfs.roll_no NOT IN (SELECT roll_no FROM class_hist)
AND UPPER(TRIM(cfs.D_AREA)) = 'US'
AND (cfs.master_cd IN (SELECT msk5.msk5_master_cd from msk5_mst_tbl as msk5 WHERE cfs.master_cd=msk5.msk5_master_cd and msk5_m_code=9)
OR cfs.master_cd IS NULL)
group by cfs.roll_no;

你能帮我解决这个错误吗？

提前致谢。

Answer 1

 SELECT 
 cfs.roll_no,   
 max(cclas.crdm_cd) as crdm_cd,
 max(cclas.kjtm_cd) as kjtm_cd
 FROM(select cf.* from cust_focus cf
 join class_hist ch on cf.roll_no!=ch.roll_no
join msk5_mst_tbl msk5 on cf.master_cd = msk5.msk5_master_cd where 
msk5_m_code=9))cfs
LEFT JOIN cust_class cclas
ON (cfs.CF_CLAS_NO = cclas.CLAS_NO
AND cfs.DFS_CD = cclas.DFS_CD 
AND cclas.D_AREA = 'US' 
AND cclas.active_flag = 'Y')
AND UPPER(TRIM(cfs.D_AREA)) = 'US'
OR cfs.master_cd IS NULL

虽然如此多的连接会影响性能!!仅支持多个连接子查询。

下面的查询没有任何问题。

select * from (select id from test where id>10) ajoin (select id from test where id>20) b on a.id=b.id;

在您的情况下，两个过滤器都用于同一个表(cust_focus)，否则您可以像上面的例子一样在不同的表上应用过滤器。