java - 选择与HQL分组不同

在我的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”结果，但是，如果我删除了查询的最后一行，它将返回正确的结果，但是我需要将上述结果限制为我的结果。

有人可以帮忙吗？

最佳答案

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

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

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
  )