mysql - 在 where 子句中将 select 输出引用为表

Question

我有一个查询，我想执行类似的操作

select *
from (query which i wrote) as x
where
 (select count(*)
  from x as y
  where x.location=y.location
  and x.count>=y.count)<=3;

出现错误

我可以添加我编写的查询，而不是 x。但查询量相当大。当我尝试上面的查询时，它给出表不存在错误。有没有办法执行上述操作？请帮助我。

Answer 1

您不能重复使用这样的表别名。相反，您需要复制子查询。或者使用变量:

select q.*
from (select q.*,
             (@rn := if(@l = location, @rn + 1,
                        if(@l := location, 1, 1)
                       )
             ) as rn
      from (query which i wrote) q cross join
           (select @l := '' , @rn := 0) params
      order by location, count desc
     ) q
where rn <= 3;