HQL：是否可以在子查询上执行INNER JOIN？ [英] HQL: Is it possible to perform an INNER JOIN on a subquery?

问题描述

上图是简化版本的数据库结构，用于通过时间记录项目位置。我写了下面的SQL查询，它返回每个位置的当前物品库存：

The diagram above is a simplified version of the database structure that I use to log item locations through time. I wrote the following SQL query which returns the current item inventory of each location:

select * 
from ItemLocationLog l
inner join 
(select g.idItemLocationLog, max(g.dateTime) as latest
from ItemLocationLog g
group by g.idItem)
as i 
on l.idItem = i.idItem and l.dateTime = i.latest

我希望将其转换为HQL，但我没有找到在子查询上执行INNER JOIN的语法，而且似乎不支持这种情况。有没有办法将上述内容转换为HQL（或Criteria），还是必须在这种情况下使用标准SQL查询？谢谢。

The problem I'm having is that I want to convert that to HQL, but I haven't found the syntax to perform an INNER JOIN on a subquery, and it seems like this is not supported. Is there a way to convert the above to HQL (or a Criteria) or will I have to use a standard SQL query in this case? Thanks.

推荐答案

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-subqueries

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-subqueries

请注意，HQL子查询只能出现在select或where子句中。

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

您可以重写查询，以便子查询是where子句的一部分。在子查询中引用l.idItem

You can rewrite the query so that the subquery is part of the where clause instead. Referencing the l.idItem in the subquery

这篇关于HQL：是否可以在子查询上执行INNER JOIN？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！