关于同一张表中DISTINCT问题的解决。

[md50]

最近遇到一个比较头痛的问题，请各位高手协助解决，数据结构如下：

UserID Scores
20005 23
20003 25
20005 30
20005 27
20002 50
20001 15
... ...

UserID为varchar型
Scores为int型

我想通过Select来列出Scores排行最高的三个不同的UserID的值出来，结果如下：
UserID Scores
20002 50
20005 30
20003 25

错误的写法如下：
SELECT * from data where userid in (SELECT DISTINCT top 3 UserIDFROM data order by UserID desc) order by Scores desc

请问搞手应该怎么写？

[md50]

难道我这种想法是不可行的吗？没有人这么做过？

[zjcxc]

--示例

--示例数据
create table data(UserID varchar(10),Scores int)
insert data select 20005,23
union all select 20003,25
union all select 20005,30
union all select 20005,27
union all select 20002,50
union all select 20001,15
go

--查询
SELECT distinct a.* from data a,(
SELECT top 3 Scores,UserID=min(UserID)
from data
group by Scores
order by Scores desc
)b where a.UserID=b.UserID and a.Scores=b.Scores
order by a.Scores desc
go

--删除测试
drop table data

/*--结果
UserID Scores
---------- -----------
20002 50
20005 30
20005 27

（所影响的行数为 3 行）
--*/

[fengbo81]

select top 3 userid,max(score) from data group by userid order by max(score) desc

[xluzhong]

select top 3 * from data a where not exists(select 1 from data where userid=a.userid and scores>a.scores) order by scores desc
--or
select top 3 userid,max(scores) as scores
from data
group by userid
order by max(scores) desc

[xluzhong]

create table data(UserID varchar(10),Scores int)
insert data select 20005,23
union all select 20003,25
union all select 20005,30
union all select 20005,27
union all select 20002,50
union all select 20001,15

select top 3 * from data a where not exists(select 1 from data where userid=a.userid and scores>a.scores) order by scores desc
--or
select top 3 userid,max(scores) as scores
from data
group by userid
order by max(scores) desc

[zjcxc]

--示例

--示例数据
create table data(UserID varchar(10),Scores int)
insert data select 20005,23
union all select 20003,25
union all select 20005,30
union all select 20005,27
union all select 20002,50
union all select 20001,15
go

--查询
SELECT distinct a.* from data a,(
SELECT top 3 Scores=max(Scores),UserID
from data
group by UserID
order by Scores desc
)b where a.UserID=b.UserID and a.Scores=b.Scores
order by a.Scores desc
go

--删除测试
drop table data

/*--结果
UserID Scores
---------- -----------
20002 50
20005 30
20003 25

（所影响的行数为 3 行）
--*/

[zjcxc]

我的第一个搞错了,最后这个才对.

[oursaviour]

select top 3 UserID,max(Scores) as MaxScores from data
group by UserID order by MaxScores desc

[lxzm1001]

create table userd
(userid char(5),
scores int)
insert userd select '20005',23
union select '20003',25
union select '20005',30
union select '20005',27
union select '20002',50
union select '20001',15
select * From userd

select top 3 * from (select userid,max(scores) as scores from userd group by userid) as userdd order by userdd.scores desc