求一难度较高的SQL语句
现在有表T1:
GroupID     Hits           UserID
4 954 20
3 678 20
2 500 21
4 100 23
2 49 20
1 25 20

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

高手们,这能不能用SQL语句来实例啊????     拜托了,   谢谢!


------解决方案--------------------
set nocount on
create table test(GroupID int,Hits int,UserID int)
insert test 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

select * into # from
(
select * from test a where not exists
(
select 1 from test where UserID=a.UserID and Hits> a.Hits
)
)a

select GroupID from # a where UserID=
(
select top 1 UserID from # where GroupID=a.GroupID
)

drop table test,#

--result
GroupID
-----------
4
2
------解决方案--------------------
declare @t table(GroupID int, Hits int, UserID int)
insert @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

----方法1:
select distinct(GroupID) from @t as a where not exists(select 1 from @t where UserID = a.UserID and Hits > a.Hits)
----方法2:
select distinct(GroupID) from @t as a where Hits = (select max(Hits) from @t where UserID = a.UserID)


/*结果
GroupID
-----------
2
4
*/
------解决方案--------------------
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


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