java - HQL以获取在一组中拥有所有项目的元素

当前，我有一个HQL查询，该查询返回一组指定奖励中所有拥有ANY奖励的会员：

from Member m left join m.awards as a where a.name in ("Trophy","Ribbon");

我现在需要的是HQL，它将退回所有拥有该奖项集中指定的所有奖项的会员。

因此，假设此数据：

Joe has Trophy, Medal
Sue has Trophy, Ribbon
Tom has Trophy, Ribbon, Medal

上面的查询将返回Joe，Sue和Tom，因为这三个人都至少拥有Trophy或Ribbon之一。但是我只需要归还Sue和Tom，因为他们是唯一拥有所有指定奖项（奖杯和丝带）的人。

这是类结构（简化）：

class Member {
  private String name;
  private Set<Award> awards;
}
class Award {
  private String name;
}

最佳答案

select m from Member m left join m.awards as a where a.name in ("Trophy","Ribbon") group by m having count(a)=2