如何在JPA中按count（）订购 [英] How to order by count() in JPA

问题描述

我正在使用此JPA-Query：

I am using This JPA-Query:

SELECT DISTINCT e.label FROM Entity e 
GROUP BY e.label 
ORDER BY COUNT(e.label) DESC

我没有错误，结果几乎是正确的，但有些值是错误的（要么翻转两个值，要么完全放错一些单个值）

I get no errors and the results are sorted almost correct but there are some values wrong (either two values are flipped or some single values are completly misplaced)

编辑：

将COUNT（e.label）添加到我的SELECT子句可以解决此查询的这个问题。

Adding COUNT(e.label) to my SELECT clause resolves this problem for this query.

但是在类似的查询也包含WHERE子句，问题仍然存在：

But in a similar query which also contains a WHERE clause the problem persists:

SELECT DISTINCT e.label, COUNT(e.label) FROM Entity e 
WHERE TYPE(e.cat) = :category 
GROUP BY e.label 
ORDER BY COUNT(e.label) DESC

推荐答案

您可能需要包含 COUNT（e.label）在SELECT子句中：

You might need to include the COUNT(e.label) in your SELECT clause:

SELECT DISTINCT e.label, COUNT(e.label) 
FROM Entity e 
GROUP BY e.label 
ORDER BY COUNT(e.label) DESC

更新：关于第二个查询，请阅读 8.6。 EntityManager文档的多态查询。看来如果你以一种需要多个 SELECT 的方式进行查询，那么 ORDER BY 就不会工作了。使用 TYPE 关键字似乎就是这种情况。来自以上链接的引用：

UPDATE: Regarding the second query please read section 8.6. Polymorphic queries of the EntityManager documentation. It seems that if you make your queries in a way that requires multiple SELECTs, then the ORDER BY won't work anymore. Using the TYPE keyword seems to be such a case. A quote from the above link:

---

以下查询将返回所有持久对象：

---

The following query would return all persistent objects:

from java.lang.Object o // HQL only

Named接口可能由各种持久化类实现：

The interface Named might be implemented by various persistent classes:

from Named n, Named m where n.name = m.name // HQL only

请注意，最后两个查询将需要多个SQL SELECT。 这意味着order by子句无法正确排序整个结果集。（这也意味着您无法使用Query.scroll（）调用这些查询。）

Note that these last two queries will require more than one SQL SELECT. This means that the order by clause does not correctly order the whole result set. (It also means you can't call these queries using Query.scroll().)

这篇关于如何在JPA中按count（）订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！