Java 使用 group by 的 SQL 连接的 HQL 版本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3402583/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HQL version of a SQL join with group by
提问by kousen
I have two tables, Band and Votes. Band has a name and an id, and Votes has a total_votes column and a foreign key called band_id pointing to band.id.
我有两张桌子，乐队和投票。Band 有一个名称和一个 id，Votes 有一个 total_votes 列和一个名为 band_id 的外键，指向 band.id。
I have lots of votes, saved at various dates. What I want to do is find the maximum value of the total_votes column for each band. The following SQL query works:
我有很多选票，保存在不同的日期。我想要做的是找到每个波段的 total_votes 列的最大值。以下 SQL 查询有效：
select b.name,max(v.total_votes) as total from band b, votes v
where b.id=v.band_id
group by b.name order by total desc;
The system I'm working with, though, uses Hibernate. I'd like to re-write that SQL query as either HQL or a Hibernate criteria query.
不过，我正在使用的系统使用 Hibernate。我想将该 SQL 查询重写为 HQL 或 Hibernate 条件查询。
Is this easy and I'm just missing it? Thanks for any help.
这很容易，我只是想念它吗？谢谢你的帮助。
采纳答案by Pascal Thivent
In HQL, could you give this a try:
在 HQL 中，你能不能试试这个：
select band.name, max(vote.totalVotes)
from Band band
join band.votes vote
group by band.name
order by max(vote.totalVotes) desc
This assumes there is a one-to-many association between Bandand Votes(actually, providing the object model is very helpful when working with HQL and/or the Criteria API since you are querying the object model).
这是假设之间存在一个一对多的关联Band和Votes（实际上，提供对象模型HQL和/或标准API，因为要查询的对象模型工作时非常有用）。
Just in case, here is the relevant section of the documentation:
以防万一，这里是文档的相关部分：
14.12. The group by clause
A query that returns aggregate values can be grouped by any property of a returned class or components:
select cat.color, sum(cat.weight), count(cat) from Cat cat group by cat.color select foo.id, avg(name), max(name) from Foo foo join foo.names name group by foo.idA having clause is also allowed.
select cat.color, sum(cat.weight), count(cat) from Cat cat group by cat.color having cat.color in (eg.Color.TABBY, eg.Color.BLACK)SQL functions and aggregate functions are allowed in the having and order by clauses if they are supported by the underlying database (i.e., not in MySQL).
select cat from Cat cat join cat.kittens kitten group by cat.id, cat.name, cat.other, cat.properties having avg(kitten.weight) > 100 order by count(kitten) asc, sum(kitten.weight) descNeither the group by clause nor the order by clause can contain arithmetic expressions. Hibernate also does not currently expand a grouped entity, so you cannot write group by cat if all properties of cat are non-aggregated. You have to list all non-aggregated properties explicitly.
14.12. group by 子句
返回聚合值的查询可以按返回的类或组件的任何属性进行分组：
select cat.color, sum(cat.weight), count(cat) from Cat cat group by cat.color select foo.id, avg(name), max(name) from Foo foo join foo.names name group by foo.id也允许使用 have 子句。
select cat.color, sum(cat.weight), count(cat) from Cat cat group by cat.color having cat.color in (eg.Color.TABBY, eg.Color.BLACK)如果底层数据库支持 SQL 函数和聚合函数（即在 MySQL 中不支持），则它们可以在 have 和 order by 子句中使用。
select cat from Cat cat join cat.kittens kitten group by cat.id, cat.name, cat.other, cat.properties having avg(kitten.weight) > 100 order by count(kitten) asc, sum(kitten.weight) descgroup by 子句和 order by 子句都不能包含算术表达式。Hibernate 目前也不会扩展分组实体，因此如果 cat 的所有属性都未聚合，则无法编写 group by cat。您必须明确列出所有非聚合属性。
