mysql - 如何获得每组的第一行？

Question

我有这样的查询:

select count(1) num, business_id, category_id
from mytable
group by business_id, category_id
order by num desc

结果是这样的:

// res
+-----+-------------+-------------+
| num | business_id | category_id |
+-----+-------------+-------------+
| 22  | 5543        | 8           |
| 19  | 4352        | 8           |
| 13  | 3242        | 11          |
| 10  | 2132        | 11          |
| 7   | 6832        | 8           |
+-----+-------------+-------------+

现在我想获取每个 category_id 的第一行。所以它一定是最大的 num 和它的 business_id。所以预期的结果是:

// expected res
+-----+-------------+-------------+
| num | business_id | category_id |
+-----+-------------+-------------+
| 22  | 5543        | 8           |
| 13  | 3242        | 11          |
+-----+-------------+-------------+

我该怎么做？

Answer 1

如果你的 MySQL 版本支持 ROW_NUMBER + 窗口函数，可以尝试使用ROW_NUMBER得到最大的num通过 category_id

查询#1

SELECT num,business_id,category_id
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY num desc) rn
    FROM (
        select count(1) num, business_id, category_id
        from mytable
        group by business_id, category_id
    ) t1
) t1
WHERE rn = 1

<表类="s-表"><头><日>数 business_idcategory_id<正文>225543813324211

---

View on DB Fiddle