HQL版本的SQL与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，因为你正在查询对象模型）。

以下是文档的相关部分：

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:

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