java - 在 HQL 中选择 distinct with group by 和 having

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”结果，但是，如果我删除查询的最后一行，它会返回正确的结果，但我需要根据上述 having 条件限制我的结果。

有人能帮忙吗？

Answer 1

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

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

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
  )