HQL/SQL根据计数选择前10条记录

I have 2 tables:

我有两个表:

CATEGORY (id)
POSTING (id, categoryId)

I am trying to write an HQL or SQL query to find top 10 Categories which have the most number of Postings.

我正在尝试编写一个HQL或SQL查询来查找发布数量最多的10个类别。

Help is appreciated.

帮助是感激。

4 个解决方案

#1

SQL query:

SQL查询:

SELECT  c.Id, sub.POSTINGCOUNT
FROM CATEGORY c where c.Id IN
( 
    SELECT TOP 10 p.categoryId
    FROM POSTING p
    GROUP BY p.categoryId 
    order by count(1) desc
)

HQL:

Session.CreateQuery("select c.Id
        FROM CATEGORY c where c.Id IN
        ( 
            SELECT  p.categoryId
            FROM POSTING p
            GROUP BY p.categoryId 
            order by count(1) desc
        )").SetMaxResults(10).List();

http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

#2

In SQL you can do this:

在SQL中，您可以这样做:

SELECT c.Id, sub.POSTINGCOUNT
FROM CATEGORY c 
INNER JOIN 
( 
    SELECT p.categoryId, COUNT(id) AS 'POSTINGCOUNT'
    FROM POSTING p
    GROUP BY p.categoryId
) sub ON c.Id = sub.categoryId
ORDER BY POSTINGCOUNT DESC
LIMIT 10

#3

SQL can be like :

SQL可以是:

SELECT c.* from CATEGORY c, (SELECT count(id) as postings_count,categoryId
FROM POSTING 
GROUP BY categoryId ORDER BY postings_count
LIMIT 10) d where c.id=d.categoryId

This output can be mapped to the Category entity.

这个输出可以映射到类别实体。

#4

I know that is an old question, but i reached a satisfatory answer.

我知道这是个老问题，但我得到了满意的回答。

JPQL:

//the join clause is necessary, because you cannot use p.category in group by clause directly
@NamedQuery(name="Category.topN", 
     query="select c, count(p.id) as uses 
            from Posting p 
            join p.category c 
            group by c order by uses desc ")

Java:

List<Object[]> list = getEntityManager().createNamedQuery("Category.topN", Object[].class)
            .setMaxResults(10)
            .getResultList();
//here we must made a conversion, because the JPA cannot order using a non select field (used stream API, but you can do it in old way)
List<Category> cats = list.stream().map(oa -> (Category) oa[0]).collect(Collectors.toList());

#1