hadoop - Hive:在选择中使用选择

Question

首先我使用查询:

select name
from tab1
where id in (select id
             from (select id,count(id) as a 
                   from tab2
                   group by id
                   order by a desc limit 1) ;

我开始知道在 hive 中不可能在 select 中选择 select。所以我用变量修改了它。

set var1= select count(id) as a from tab2 group by id order by a desc limit 1;

select name from tab1 group by name having count(id)='${hiveconf:var1}';

但是在 '${hiveconf:var1}' 的位置，查询被替换并再次出现相同的错误。

有什么办法吗？

Answer 1

select  t1.name 

from            tab1 t1 

        join   (select      id
                           ,count(*) as cnt
                from        tab2 
                group by    id 
                order by    cnt desc 
                limit       1
                ) t2 

        on      t2.id = t1.id