这可能看起来很复杂，但它只会将数据汇总 一次 ，然后通过它对结果进行排名并仅选择第一个

select userid, websiteid, visits
from
(
select 
 userid, websiteid, visits,
 @r := case when @u=userid then @r+1 else 1 end r,
 @u := userid
from
(select @u:=null) x,
(select userid, websiteid, count(*) visits
 from visit
 group by userid, websiteid
 order by userid, visits desc) y
) z
where r=1

内部选择会为每个用户-网站组合生成计数，并按访问次数排序。然后通过中间查询对记录per user进行排名，并在列中给出排名r。

这是一个变体，它将显示具有平等排名的用户的所有网站。与上一个查询的区别在于，如果网站A和B都有来自用户X的10次访问，则结果中将同时列出A和B，而前一个查询则随机选择一个来显示。

select userid, websiteid, visits
from
(
select 
 userid, websiteid, visits,
 @r := case
    when @u=userid and @v=visits then @r  # same rank
    when @u=userid then @r+1              # next rank
    else 1                                # different user
    end r,
 @u := userid, @v := visits
from
(select @u:=null, @v:=null) x,
(select userid, websiteid, count(*) visits
 from visit
 group by userid, websiteid
 order by userid, visits desc) y
) z
where r=1

这是使用的测试表

create table visit (userid int, websiteid int);
insert into visit select 1,1;
insert into visit select 1,2;
insert into visit select 1,1;
insert into visit select 1,4;
insert into visit select 2,1;
insert into visit select 2,2;
insert into visit select 2,5;
insert into visit select 2,2;