Java jpql 从每组中选择一个最大行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4276319/
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
Jpql selecting the one maximum row from each group
提问by Rares Musina
I am relatively new to JPA and I would like to solve the following problem using jpql exclusively (note that the implementation I am using is Datanucleus): I have a table of versioned entities, and I would like to get the latest version for all entities in the table (i.e. I have an entity class which has an id (which uniquely identifies a row, an entityId (which identifies the entity itself throughout versions) and a timestamp; I would like to get the latest version entity for all entityId). My current code goes as follows:
我对 JPA 比较陌生，我想专门使用 jpql 解决以下问题（请注意，我使用的实现是 Datanucleus）：我有一个版本化实体表，我想获取所有实体的最新版本在表中（即我有一个实体类，它有一个 id（它唯一标识一行、一个 entityId（它在整个版本中标识实体本身）和一个时间戳；我想获取所有 entityId 的最新版本实体）。我当前的代码如下：
String innerQueryString = "SELECT entity.entityId, max(entity.timestamp) " +
"FROM Entity entity" +
"GROUP BY entity.entityId";
Query getQuery = getEntityManager().createQuery(innerQueryString);
List<Object[]> queryRes = getQuery.getResultList();
List<IEntity> ret = new ArrayList<IEntity>();
for (Object[] res : queryRes) {
ret.add(getEntity((Long)res[0], (Date)res[1]));
}
return ret;
Where getEntity gets the entity data for the specified entityId, timestamp. I have found several resources on how this code would work in sql here http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/but I cannot manage to create a jpql version of it. Help will be greatly appreciated, thanks.
其中 getEntity 获取指定 entityId 的实体数据，时间戳。我在这里找到了一些关于此代码如何在 sql 中工作的资源http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in- sql/但我无法创建它的 jpql 版本。帮助将不胜感激，谢谢。
采纳答案by axtavt
If timestamps are unique per entityId, you can write something like this:
如果timestamps 是唯一的 per entityId，你可以这样写：
SELECT e FROM Entity e
WHERE e.timestamp = (SELECT MAX(ee.timestamp) FROM Entity ee WHERE ee.entityId = e.entityId)
