mysql - 在 SQL 中使用having

Question

我想合并以下查询以获得我想要的答案。但我好像不太明白。

查询 1:

select a.idlisting, count(p.idpostulant) as Total_postulants
from postulations p
inner join listings a on p.idlisting = a.idlisting
where a.idate >= '2014-07-01'
and a.idate < '2014-08-01'
group by a.idlisting
having total_postulants > 500

根据第一个查询得到的答案，我完成了 idlisting 子句

**Query 2:**

select count(distinct p.idpostulant)
from postulations p
inner join listings a on p.idlisting = a.idlisting
where a.idlisting in ('1001972582',
'1001964448',
'1001926936')

答案应该是订阅拥有超过 500 个假设的所有列表的唯一假设的数量。

请告诉我是否可以理解。

感谢您的帮助。

Answer 1

只需使用join 和子查询:

select count(distinct p.idpostulant)
from postulations p inner join
     listings a
     on p.idlisting = a.idlisting inner join
     (select a.idlisting, count(p.idpostulant) as Total_postulants
      from postulations p inner join
           listings a
           on p.idlisting = a.idlisting
      where a.idate >= '2014-07-01' and a.idate < '2014-08-01'
      group by a.idlisting
      having total_postulants > 500
     ) filter
     on a.idlisting = filter.idlisting;

编辑:

您可以通过删除外部查询中的连接之一来稍微简化上述查询:

select count(distinct p.idpostulant)
from postulations p inner join
     (select a.idlisting, count(p.idpostulant) as Total_postulants
      from postulations p inner join
           listings a
           on p.idlisting = a.idlisting
      where a.idate >= '2014-07-01' and a.idate < '2014-08-01'
      group by a.idlisting
      having total_postulants > 500
     ) filter
     on p.idlisting = filter.idlisting;