这可能看起来很复杂，但它只会将数据汇总 一次 ，然后通过它对结果进行排名并仅选择第一个
select userid, websiteid, visitsfrom(select userid, websiteid, visits, @r := case when @u=userid then @r+1 else 1 end r, @u := useridfrom(select @u:=null) x,(select userid, websiteid, count(*) visits from visit group by userid, websiteid order by userid, visits desc) y) zwhere r=1
内部选择会为每个用户-网站组合生成计数，并按访问次数排序。然后通过中间查询对记录
per user进行排名，并在列中给出排名
r。
这是一个变体，它将显示具有平等排名的用户的所有网站。与上一个查询的区别在于，如果网站A和B都有来自用户X的10次访问，则结果中将同时列出A和B，而前一个查询则随机选择一个来显示。
select userid, websiteid, visitsfrom(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 := visitsfrom(select @u:=null, @v:=null) x,(select userid, websiteid, count(*) visits from visit group by userid, websiteid order by userid, visits desc) y) zwhere 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;
