Jpql:没有Group By

Question

我有(工作中的) SQL查询

   select team
   from team
   join account a on team.account_id = a.id
   left join exchange_order eo on a.id = eo.account_id
   left join exchange_order_transaction eot on eo.id = eot.order_id
   having count(eot.id) = 0;

并希望将其转换为jpql。

    @Query("SELECT COUNT(t) " +
            "FROM Team t " +
            "         left join Account a on t.account = a.id " +
            "         left join ExchangeOrder eo on a.id = eo.account " +
            "         left join ExchangeOrderTransaction eot on eo.id = eot.order " +
            "HAVING count(eot) = 0")
    long countWithoutTransactions();

问题是，JPQL似乎不允许没有"group by“。但在这种情况下，我不想要任何组的东西。只想计算所有行的数量。

有没有什么方法可以泛化"group by“，或者我应该如何构造查询？

Answer 1

我认为您只需要对exchange_order_transaction表执行一次左反联接。使用以下查询：

select team.*
from team
join account a on team.account_id = a.id
left join exchange_order eo on a.id = eo.account_id
left join exchange_order_transaction eot on eo.id = eot.order_id
where eot.id is null;