可以在另一个查询的结果集上选择HQL吗？

Can HQL Select on the result set of another query?
For example:

可以在另一个查询的结果集上选择HQL吗？例如：

SELECT COUNT(*) FROM (SELECT * FROM Table)

I can do it in SQL but when I tried like above in HQL but the it just show me syntax error "unexpected token: ( near line 1, column 22 ..."

我可以在SQL中做到这一点，但是当我在HQL中尝试上面但它只是向我显示语法错误“意外令牌:(靠近第1行，第22列......”

3 个解决方案

#1

HQL does support subqueries, however they can only occur in the select or the where clause. The example you provide would best be wrote as a straight statement in HQL. For example:

HQL支持子查询，但它们只能出现在select或where子句中。您提供的示例最好在HQL中作为直接语句编写。例如：

select count(*) from table t  (where table is the entity name)

If the query involves a more complicated statement than (select * from Table), I would recommend putting this logic into a view and then creating an entity based off of this view.

如果查询涉及比（select * from Table）更复杂的语句，我建议将此逻辑放入视图中，然后根据此视图创建实体。

For databases that support subselects, Hibernate supports subqueries within queries. A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed.

对于支持子选择的数据库，Hibernate支持查询中的子查询。子查询必须用括号括起来（通常通过SQL聚合函数调用）。甚至允许相关子查询（引用外部查询中的别名的子查询）。

Example

例

from DomesticCat as cat
where cat.name not in (
    select name.nickName from Name as name
)

#2

Using subquery as you desire is not possible. One way is using a distinct this way:

根据需要使用子查询是不可能的。一种方法是使用不同的方式：

SELECT COUNT(DISTINCT t.id) FROM table t INNER JOIN t.list l
     WHERE t.status = 'ST1' AND l.status = 'ST2'"

I used the inner join to express a select repetition

我使用内连接来表达选择重复

#3

there is no way to do subquery in from clause in HQL even if the database support it, I solved this problem by putting the query into the sql as a store procedure, then call the procedure in HQL. For example:

即使数据库支持它，也无法在HQL的from子句中执行子查询，我通过将查询作为存储过程放入sql来解决此问题，然后在HQL中调用该过程。例如：

Insert the procedure into your sql:

将过程插入到sql中：

DELIMITER $$
CREATE PROCEDURE `procedure_name`(
  `arg_name` INT,
) BEGIN
     your query here
END;
$$
DELIMITER ;

Then, if you use hibernate, call this procedure from java code as below:

然后，如果您使用hibernate，请从java代码中调用此过程，如下所示：

Query query = session.createSQLQuery("CALL procedure_name(:arg_name)");
query.setParameter("arg_name", args);
List list = query.list();

Hope this can help you.

希望这可以帮到你。

#1