java - “组语句”中的 JPA 未知列别名

Question

在 java SQL 上运行此查询时出现错误，但当我在 SQL 编辑器上测试它时它可以工作

@Query(value = "SELECT l.loan, max(i.date) as dDate, tr.collecty, sum(i.expectedAmount) as amount,"
            + "(select CASE WHEN (br.stype = 'x') THEN br.idNumber ELSE br.np END as type from Bower br inner join Leds ld on br.id = ld.bowerId where ld.loan = :loan) as bId "
            + "FROM Loan l INNER JOIN Inst i ON l.loan = i.loan INNER JOIN TList tr ON l.loan = tr.loan WHERE l.loan = :loan GROUP BY l.loanId, tr.collecty, bId")

并得到错误

引起：java.sql.SQLSyntaxErrorException：“组语句”中的未知列“bId”

如何解决？

标签： javasqljpa

Answer 1

group by 的执行顺序在查询中的 select 之前。因此，在您按 bId 分组时，选择查询尚未执行，它不会识别 bId 列或您在选择时指定的任何别名。