求一有技术含量的SQL语句(高手请进)

[pcvc]

现在有表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语句来实例啊???? 拜托了, 谢谢!

[pcvc]

就拿这表:
GroupID Hits UserID
4 954 20
3 678 20
2 500 21
4 100 23
2 49 20
1 25 20

取出来的GroupID应为:
GroupID
4
2

[bill024]

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

[hellowork]

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
*/

[simonhehe]

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

[pcvc]

结帖, 谢谢你们, 各你们学习!

[hellowork]

如果希望同时显示每个GroupId的Hits,可以这样:
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 GroupID,max(Hits) as Hits from @t as a where not exists(select 1 from @t where UserID = a.UserID and Hits > a.Hits) group by GroupId
----方法2:
select GroupID,max(Hits) as Hits from @t as a where Hits = (select max(Hits) from @t where UserID = a.UserID) group by GroupId
----方法3:
select GroupID,max(Hits) as Hits from @t as a where Hits = (select top 1 Hits from @t where UserID = a.UserID order by Hits DESC) group by GroupId
----方法4:
select a.GroupID,max(a.Hits) as Hits from @t as a
inner join (select UserID,max(Hits) as Hits from @t group by UserID) as b on a.UserID = b.UserID and a.Hits = b.Hits
group by a.GroupID

/*结果
GroupID Hits
----------- -----------
2 500
4 954
*/