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(或标准)，或者在这种情况下我是否必须使用标准 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

请注意，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屋！