用户点击分析-优快云博客

本文介绍了一种从用户点击数据中提取每个用户点击次数最多组的方法。通过三种不同的SQL查询方法展示了如何实现这一目标，并确保每位用户的选取唯一且不会重复选择相同的组。

有表T1:
GroupID Hits UserID
495420
367820
250021
410023
24920
12520

要求:取每一个用户点击数最多的GroupID,每一用户只取一个,前一个用户取过的GroupID后面的不取.如此表中:用户ID为20的取GroupID为4,用户ID为21的取GroupID为2,用户ID为23的不取,因为用户ID为23对应的GroupID为4在前面用户ID为20的取过了!

------------------------------------------------------------------------------------------

declare @t table(GroupID int,Hits int,UserID int)
insert into @t
select 4,954,20 union all
select 3,678,20 union all
select 2,500,21 union all
select 4,100,23 union all
select 2,49,20 union all
select 1,25,20

-- Method 1
select GroupID,Hits = max(Hits)
from @t
where Hits in (select Hits = max(Hits) from @t group by UserID)
group by GroupID
order by GroupID desc

-- Method 2
select GroupID,Hits = Max(a.Hits)
from @t a join (select UserID,Hits = Max(Hits) from @t group by UserID) b on a.UserID = b.UserID and a.Hits = b.Hits
group by GroupID

-- Method 3
select a.GroupID,Hits = Max(a.Hits)
from (select GroupID,Hits = Max(Hits) from @t group by GroupID) a
join (select UserID,Hits = Max(Hits) from @t group by UserID) b on a.Hits = b.Hits
group by GroupID