SQL:在每个类别中找到最热门的文章 [英] SQL: Find top-rated article in each category

问题描述

我有一个表articles，其中的字段为id，rating(1-10之间的整数)和category_id(代表它所属的类别的整数).

I have a table articles, with fields id, rating (an integer from 1-10), and category_id (an integer representing to which category it belongs).

如何在一个MySQL查询中从每个类别中找到评分最高的单篇文章?我想ORDER BY和LIMIT通常是我会找到最高评价的文章的方式，但我不确定如何将其与分组混合以获得期望的结果，即使可以的话. (依赖子查询可能是一个简单的答案，但是ewwww.有更好的方法吗?)

How can I, in one MySQL query, find the single article with the highest rating from each category? ORDER BY and LIMIT would usually be how I would find the top-rated article, I suppose, but I'm not sure how to mix that with grouping to get the desired result, if I even can. (A dependent subquery would likely be an easy answer, but ewwww. Is there something better?)

对于以下数据:

id | category_id | rating
---+-------------+-------
 1 |           1 |     10
 2 |           1 |      8
 3 |           2 |      7
 4 |           3 |      5
 5 |           3 |      2
 6 |           3 |      6

我希望返回以下内容:

id | category_id | rating
---+-------------+-------
 1 |           1 |     10
 3 |           2 |      7
 6 |           3 |      6

推荐答案

尝试一下

SELECT id, category_id, rating
FROM   articles a1
WHERE rating = 
(SELECT MAX(a2.rating) FROM articles a2 WHERE a1.category_id = a2.category_id)

OR

SELECT * FROM (SELECT * FROM articles ORDER BY rating DESC) AS a1 GROUP BY a1.rating;

这篇关于SQL:在每个类别中找到最热门的文章的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！