Java 具有最大值的实体的 HQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4497624/
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 query for entity with max value
提问by rjsang
I have a Hibernate entity that looks like this (accessors ommitted for brevity):
我有一个看起来像这样的 Hibernate 实体（为简洁起见，省略了访问器）：
@Entity
@Table(name="FeatureList_Version")
@SecondaryTable(name="FeatureList",
pkJoinColumns=@PrimaryKeyJoinColumn(name="FeatureList_Key"))
public class FeatureList implements Serializable {
@Id
@Column(name="FeatureList_Version_Key")
private String key;
@Column(name="Name",table="FeatureList")
private String name;
@Column(name="VERSION")
private Integer version;
}
I want to craft an HQL query that retrieves the most up to date version of a FeatureList. The following query sort of works:
我想制作一个 HQL 查询来检索 FeatureList 的最新版本。以下查询类型有效：
Select f.name, max(f.version) from FeatureList f group by f.name
The trouble is that won't populate the key field, which I need to contain the key of the record with the highest version number for the given FeatureList. If I add f.key in the select it won't work because it's not in the group by or an aggregate and if I put it in the group by the whole thing stops working and it just gives me every version as a separate entity.
问题是不会填充键字段，我需要包含给定 FeatureList 的具有最高版本号的记录的键。如果我在选择中添加 f.key 它将不起作用，因为它不在 group by 或聚合中，如果我将它放在 group by 中，整个事情就会停止工作，它只会将每个版本作为单独的实体提供给我。
So, can anybody help?
那么，有人可以帮忙吗？
采纳答案by axtavt
The straightforward version of this query looks like this (assuming that (name, version)pairs are unique):
此查询的简单版本如下所示（假设(name, version)对是唯一的）：
select f from FeatureList f
where f.version =
(select max(ff.version) from FeatureList ff where ff.name = f.name)
回答by Adeel Ansari
How about this,
这个怎么样，
from FeatureList fl where (fl.name, fl.version) in (
select f.name, max(f.version) from FeatureList f group by f.name)
Note: Try with the createNativeQuery(), its a valid Oracle query, not sure about any other database.
注意：尝试使用createNativeQuery()，它是一个有效的 Oracle 查询，不确定任何其他数据库。
One question, is the pair, nameand version, unique? If yes, then its fine, otherwise if the pair is not unique, what do you think how a key will be selected? Suppose one of the record from the original query is,
一个问题，这对，name和version，是唯一的吗？如果是，那很好，否则如果这对不是唯一的，你认为如何选择一个键？假设来自原始查询的记录之一是，
f.name max(f.version)
------ --------------
Django 1.2
and assume there are 3 keys having the same pair. Now answer, which key should be assigned to this record? I hope you are getting my point.
并假设有 3key秒具有相同的对。现在回答，应该为该记录分配哪个键？我希望你明白我的意思。
回答by Adeel Ansari
I made a scenario here,
我在这里做了一个场景，
Table
桌子
key name version
----------- -------------------- -----------
1 adeel 1
2 adeel 2
3 adeel 3
4 ahmad 1
5 ahmad 2
6 ahmad 3
7 ahmad 4
8 ansari 1
9 ansari 2
10 ansari 3
Result using your original query
使用原始查询的结果
>> select f.name, max(f.version) from FeatureList f group by f.name
name max(f.version)
-------------------- ------------
adeel 3
ahmad 4
ansari 3
Result using your desired query
使用您想要的查询的结果
>> select fl.* from FeatureList fl
where (fl.name, fl.version) in (select f.name, max(f.version)
from FeatureList f group by f.name);
key name max(fl.version)
----------- -------------------- -----------
3 adeel 3
7 ahmad 4
10 ansari 3
NB: Tried it using MySQL this time. Its working. I am pretty sure MS SQL Server also have IN (...)operator. You just need to use session.createNativeQuery()in Hibernate.
注意：这次尝试使用 MySQL。它的工作。我很确定 MS SQL Server 也有IN (...)运算符。你只需要session.createNativeQuery()在 Hibernate 中使用。
Edited to work on axtavt's answer
编辑以处理axtavt 的答案
As we found out this can be made as simple as,
正如我们发现的那样，这可以很简单，
select f from FeatureList f
where f.version =
(select max(ff.version) from FeatureList ff where ff.name = f.name)
Now try the same using Hibernate Criteria API,
现在使用Hibernate Criteria API尝试相同的方法，
DetachedCriteria versions = DetachedCriteria.forClass(FeatureList.class, "f")
.setProjection( Property.forName("f.version").max())
.add(Property.forName("f.name").eqProperty("fl.name"));
session.createCriteria(FeatureList.class, "fl")
.add( Property.forName("fl.version").eq(versions) )
.list();
回答by Matt Brock
How about using distinct + order by instead of group by?
使用distinct + order by 代替group by 怎么样？
select f.id, distinct(f.name), f.version from FeatureList f order by f.version desc
回答by Mark McKenna
Old question but I thought I'd provide my experience here as well for future users:
老问题，但我想我也会在这里为未来的用户提供我的经验：
select f from FeatureList f where f.version =
(select max(ff.version) from FeatureList ff where ff.name = f.name);
This works great, but if the following holds:
这很好用，但如果以下情况成立：
- MySql v5.5 w/ InnoDB engine
- You know exactly how many result rows you want (OC implies that s/he wants exactly 1 row, but the above query would work for a varying number of results)
- 带有 InnoDB 引擎的 MySql v5.5
- 您确切地知道您想要多少结果行（OC 意味着他/她只想要 1 行，但上述查询适用于不同数量的结果）
the following appears to be very significantly faster:
以下似乎非常快：
FROM FeatureList ORDER BY version DESC LIMIT 1;
My trials on a similar query with 700,000 records in the table takes around 0.19s for the former version and 0.05s for the latter.
我对表中有 700,000 条记录的类似查询的试验，前者大约需要 0.19 秒，后者大约需要 0.05 秒。
