java - 使用 group by 连接的 SQL 的 HQL 版本

Question

我有两个表，Band 和 Votes。 Band 有一个名称和一个 ID，Votes 有一个 total_votes 列和一个名为 band_id 的外键指向 band.id。

我有很多选票，保存在不同的日期。我想要做的是找到每个波段的 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;

不过，我正在使用的系统使用的是 Hibernate。我想将该 SQL 查询重写为 HQL 或 Hibernate 条件查询。

这容易吗，我只是想念它？感谢您的帮助。

Answer 1

在 HQL 中，你能试一试吗:

select band.name, max(vote.totalVotes)
from Band band
     join band.votes vote
group by band.name
order by max(vote.totalVotes) desc

这假设 Band 和 Votes 之间存在一对多关联(实际上，提供对象模型在使用 HQL 和/或Criteria API，因为您正在查询对象模型)。

以防万一，这里是文档的相关部分:

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.id

A 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) desc

Neither 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.