spring-data-jpa - JPQL @Query 导致 "unexpected token: ("

Question

我定义了以下方法:

@Query("SELECT AVG(total) FROM (SELECT COUNT(t.name) total FROM DataTable t GROUP BY DATE(actiontime)) result")
Long countAvg();

但是它会导致这个错误:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected     token: ( near line 1, column 24 [SELECT AVG(total) FROM (SELECT     COUNT(t.name) total FROM backend.DataTable t GROUP BY     DATE(actiontime)) result]

但是下面的 SQL 工作正常:

SELECT AVG(total) FROM (SELECT COUNT(NAME) total FROM DATA_TABLE GROUP BY DATE(actiontime)) result 

如果我理解正确的话，JPQL 在子查询方面有问题。那么我应该如何创建这种查询？

Answer 1

我认为 jpql 不支持带有 from 子句的 subselect。根据 jpa docs

Subqueries are restricted to the WHERE and HAVING clauses in this release. Support for subqueries in the FROM clause will be considered in a later release of the specification.

您可以在 @Query 注释中使用 nativeQuery = true 并将其作为 native 查询运行，或者在可能的情况下重写查询。