select A.* from A where exists ( select 1 from ( select a,row_number() over(order by count(*) desc )seq from aaaa t group by a )D where seq < 10 and A.id = D.id
select A.*,C.cn 点击数量 from A,(select distinct id,count(*) over(partition by id) cn from B order by cn desc) C where C.rownum<=10 and A.id = C.id
select 1 from (
select a,row_number() over(order by count(*) desc )seq
from aaaa t group by a
)D where seq < 10 and A.id = D.id
from A,(select distinct id,count(*) over(partition by id) cn
from B order by cn desc) C
where C.rownum<=10 and A.id = C.id