JPQL选择具有最大值的记录 [英] JPQL Selecting Record with Max Value

问题描述

我正在尝试为特定列选择具有最大值的记录，这是我到目前为止的内容:

I'm trying to select the record that has the max value for a particular column and here's what I have so far:

select o from Order o 
    where o.orderNumber = :orderNumber
    and o.version =
        (select max(o.version) from Order o where o.orderNumber = :orderNumber)

这似乎正在生成SQL:

This seems to be generating the SQL:

SELECT *
FROM   Order
WHERE  ordernumber = :orderNumber
       AND orderversion = (SELECT Max(orderversion)
                           FROM   order
                           WHERE  ordernumber = :orderNumber);

我认为以下SQL会更有效:

I think the following SQL would be more efficient:

SELECT *
FROM   order ord
       INNER JOIN (SELECT ordernumber,
                          Max (version) AS version
                   FROM   order
                   WHERE  ordernumber = :ordernumber
                   GROUP  BY ordernumber) mx
         ON ord.ordernumber = mx.ordernumber
            AND ord.version = mx.version; 

反正我可以在JPQL中表达吗?

Is there anyway I can express this in JPQL?

(大多数在线回答有关在特定字段上选择具有最大价值的记录的问题，似乎都暗示了我提出的上述JPQL ...)

(Most answers online for questions related to selecting record with max value on a particular field seem to suggest the above JPQL I had presented...)

推荐答案

否，无法使用HQL生成上述SQL查询. 文档说:

No, it's not possible to generate the above SQL query with HQL. The documentation says:

请注意，HQL子查询只能在select或 条款.

Note that HQL subqueries can occur only in the select or where clauses.

这篇关于JPQL选择具有最大值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！