按HQL和HQL选择distinct和group

Question

在我的java项目中，我需要执行HQL查询。

以下是我的HQL查询：

select count(distinct n.id)" +
            "  FROM Neighborhood n, NeighborhoodMeta meta, NeighborhoodAffordability aff, AirbnbProperty as ap" +
            "  WHERE n.id = meta.id AND n.id = aff.id AND n.id = ap.neighborhood AND aff.singleHomeValue!=null" +
            " AND (latitude >=:minLat AND latitude <=:maxLat)" +
            " AND (longitude >=:minLong " + (meridian180WithinDistance ? "OR" : "AND") + " longitude <=:maxLong) AND " +
            "acos(sin(:locationLatitude) * sin(radians(latitude)) + cos(:locationLatitude) * cos(radians(latitude)) * cos(radians(longitude) -:locationLongitude)) <=:R " +
            "GROUP BY ap.neighborhood having count(ap.id) > 19  

这个计数总是产生一个"1“结果，但是，如果我删除了最后一行查询，它会返回一个正确的结果，但是我需要将我的结果限制在上述条件下。

有人能帮忙吗？

Answer 1

您只获得1，因为您选择用于分组的不同值的计数(n.id = ap.neighborhood，因此n.id与ap.neighborhood相同)。

我假设查询的目标是计数不同的Neighborhood，它们与超过19个AirbnbPropertys相关联(当然，在应用了所有其他条件之后)。如果是这样的话，您需要的基本上是：

select count(*) from
 (select n.id
   from
   ... the rest of your query without group by ...
   group by n.id having count(ap.id) > 19
 )

但是，Hibernate不支持from子句中的子查询，因此您必须使用in操作符来解决这个问题：

select count(*) from Neighborhood n
 where n.id in 
  (select n.id
    from
    ... the rest of your query without group by ...
    group by n.id having count(ap.id) > 19
  )