mysql - 根据最大出现次数和用户名的最大值选择记录

Question

以下查询工作正常:

SELECT user.username, preference.user_id, preference.category,COUNT(*) AS occurrences 
  FROM preference inner 
  JOIN user on preference.user_id=user.userid
 where category is not null and category!=""
 GROUP BY category,user_id 
 ORDER BY occurrences DESC ;

fiddle here

我只是想要

A) 仅获取每个用户名最流行的类别(出现次数最多的类别)，因此对于上面的 fiddle ，对于 user1 来说，它将是“服装、鞋子和配饰”(出现 3 次)，对于 user2 来说，它将是“古董”(出现 3 次) )，示例中除这两行之外的所有其他行都应省略。

B)另一个查询，用于获取每个用户名的第二个最受欢迎的类别

Answer 1

您可以使用变量仅选择每个用户 2 个最受欢迎的类别

SELECT * FROM (
    SELECT * ,
    @rowNum := IF(@prevUserId = user_id,@rowNum+1,1) rowNum,
    @prevUserId := user_id
    FROM (
        SELECT user.username,@prevUserId,preference.user_id, category, COUNT(*) AS occurrences
        FROM preference inner JOIN user 
        on preference.user_id=user.userid
        where category is not null and category!=""
        GROUP BY category,preference.user_id
    ) t1 order by user_id, occurrences desc
) t1 WHERE rowNum <= 2

http://www.sqlfiddle.com/#!2/ba4ba7/24