HQL版本的SQL与group by一起加入 [英] HQL version of a SQL join with group by

问题描述

我有两张表，乐队和投票。 Band有一个名字和一个id，Votes有一个total_votes列和一个名为band_id的外键指向band.id。

我有很多选票，保存在各种日期。我想要做的是找到每个乐队的total_votes列的最大值。以下SQL查询的工作原理如下：

  select b.name，max（v.total_votes） b $ b其中b.id = v.band_id 
按b.name排序按总desc排序; 
  

我正在使用的系统使用Hibernate。我希望将该SQL查询重写为HQL或Hibernate条件查询。

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

解决方案

在HQL中，您可以试试这个：

  select band.name，max（vote.totalVotes）
 from band band 
 join band.votes vote 
 by band.name 
 order by max（vote.totalVotes）desc 
  

假设有一对一 - Band 和 Votes 之间的许多关联（实际上，提供对象模型在使用HQL和/或Criteria API，因为你正在查询对象模型）。

以下是文档的相关部分：

14.12。 group by子句

返回聚合值
的查询可以由
返回的类或组件的任何属性分组：

 从cat cat $ b $中选择cat.color，sum（cat.weight），count（cat）
 b group by cat.color 
 
从foo中选择foo.id，avg（name），max（name）
 foo加入foo.names名称
 by foo.id 
  

也可以使用having子句。

 从cat cat 
中选择cat.color，sum（cat.weight），count（cat）
 cat.color 
 cat.color （eg.Color.TABBY，eg.Color.BLACK）
  

SQL函数和集合函数$ b $如果b $ b底层数据库支持它们（即，不在
MySQL中），则允许b在
子句中进行定购。

 从cat猫中选择cat 
 
通过cat.id，cat.name，cat.other，cat加入cat.kittens kitten 
组。属性
有avg（kitten.weight）> 100 
 by count（kitten）asc，sum（kitten.weight）desc 
  

group by子句和
order by子句都不能包含算术
表达式。 Hibernate也不会
扩展一个分组实体，所以
如果cat的所有
属性都是非聚合的，你就不能编写分组cat。
您必须明确列出所有未汇总的
属性。

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.

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:

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.

Is this easy and I'm just missing it? Thanks for any help.

解决方案

In HQL, could you give this a try:

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 Band and 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).

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

这篇关于HQL版本的SQL与group by一起加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！