有哪位高手能帮小弟我看看这个sql语句如何写？待

有谁能帮我看看这个sql语句怎么写？？在线等待
我现在有个数据表：
如：
ID UserID Score Addtime
1 0815001 60 2012-3-10
2 0815001 70 2012-3-11
3 0815001 80 2012-3-8
4 0815002 75 2012-3-8
5 0815002 70 2012-3-8
6 0815003 85 2012-3-6
7 0815003 90 2012-3-7
……

实现的效果是，选择出相同的UserID的最大分数的那些记录，预期的结果是：
3 0815001 80 2012-3-8
4 0815002 75 2012-3-8
7 0815003 90 2012-3-7

希望哪位高手能帮我看下这个sql语句是怎么写的？本人是新手，不太懂这些

------解决方案--------------------

SQL code


--> 测试数据：[t1]
go
if object_id('[t1]') is not null 
drop table [t1]
go
create table [t1](
[ID] int,
[UserID] varchar(7),
[Score] int,
[Addtime] datetime
)
go
insert [t1]
select 1,'0815001',60,'2012-3-10' union all
select 2,'0815001',70,'2012-3-11' union all
select 3,'0815001',80,'2012-3-8' union all
select 4,'0815002',75,'2012-3-8' union all
select 5,'0815002',70,'2012-3-8' union all
select 6,'0815003',90,'2012-3-6' union all
select 7,'0815003',90,'2012-3-7'

--假如存在相同分数，我取日期较大的那个
;with t
as(
select ID,UserID,Score, Addtime from [t1] a
where Score=(select max(Score) from [t1] b where a.UserID=b.UserID)
)
select * from t a
where Addtime=(select MAX(Addtime) from t b where a.UserID=b.UserID)
--或者
select * from(
select ID,UserID,Score, Addtime from [t1] a
where Score=(select max(Score) from [t1] b where a.UserID=b.UserID))c
where c.Addtime=(select MAX(Addtime) from (select ID,UserID,Score,
 Addtime from [t1] a where Score=(select max(Score) from [t1] b 
 where a.UserID=b.UserID)
)d where c.UserID=d.UserID) order by ID

/*
ID    UserID    Score    Addtime
3    0815001    80    2012-03-08 00:00:00.000
4    0815002    75    2012-03-08 00:00:00.000
7    0815003    90    2012-03-07 00:00:00.000
*/

有哪位高手能帮小弟我看看这个sql语句如何写？待

相关推荐