MySQL HQL/SQL 根据计数选择前 10 条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11106524/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HQL/SQL select top 10 records based on count
提问by user879220
I have 2 tables:
我有2张桌子：
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.
帮助表示赞赏。
回答by Baz1nga
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/
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
回答by Mahmoud Gamal
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
回答by Ahamed Mustafa M
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.
此输出可以映射到类别实体。
回答by Rafael Rossignol
I know that is an old question, but i reached a satisfatory answer.
我知道这是一个老问题，但我得到了一个令人满意的答案。
JPQL:
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());
