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

Question

我有 2 个表:

CATEGORY (id)
POSTING (id, categoryId)

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

感谢您的帮助。

Answer 1

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
)

总部:

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/