这可能看起来很复杂，但它只会将数据汇总 ，然后通过它对结果进行排名并仅选择第一个
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;
602392714
清零编程群