如何获得每组的第一行?

如果您的MySQL版本支持 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

View on DB Fiddle

MAX() num 和 FIRST_VALUE() 得到 business_id 那个马克斯 号码 :

SELECT DISTINCT 
       MAX(COUNT(*)) OVER (PARTITION BY category_id) num, 
       FIRST_VALUE(business_id) OVER (PARTITION BY category_id ORDER BY COUNT(*) DESC) business_id, 
       category_id
FROM mytable
GROUP BY business_id, category_id
ORDER BY num DESC;