sql分组查询显示所有字段

[yiyi_fish]

我sql里有这样的记录：
uname score location remark
-----------------------------------------
mary 30 sh hello
mary 40 sz thanks
kate 24 sd hi
kate 45 uy sorry
moss 28 sh hello
moss 0 sz hi

我现在想查询出一样uname，对应的score是最大的那条记录。
结果是：

uname score location remark
-----------------------------------------
mary 40 sz thanks
kate 45 uy sorry
moss 28 sh hello
所有的字段都要显示出来，怎么查啊？

[sdhdy]

select  * from tb a where not exists(select 1 from tb where uname=a.uname and score>a.score)

[yiyi_fish]

一发贴格式都乱了，我再整理一下

姓名 分数 地址 备注
-----------------------------------------
mary 40 sz thanks
kate 45 uy sorry
moss 28 sh hello

[sdhdy]

create table tb(uname varchar(10), score int, location varchar(10) ,    remark varchar(10) )
go

insert tb select 'mary' ,30, 'sh'  ,       'hello' 
insert tb select 'mary', 40, 'sz'  ,       'thanks' 
insert tb select 'kate' ,24, 'sd'  ,       'hi' 
insert tb select 'kate' ,45, 'uy'  ,       'sorry' 
insert tb select 'moss', 28, 'sh'  ,       'hello' 
insert tb select 'moss', 0, 'sz'   ,      'hi' 
go
select  * from tb a where not exists(select 1 from tb where uname=a.uname and score>a.score)
go
drop table tb
go
/*
uname      score       location   remark     
---------- ----------- ---------- ---------- 
mary       40          sz         thanks
kate       45          uy         sorry
moss       28          sh         hello

（所影响的行数为 3 行）

*/

[you_tube]

用辅助列方法

create table tb(uname varchar(10), score int, location varchar(10) ,    remark varchar(10) )
go

insert tb select 'mary' ,30, 'sh'  ,       'hello' 
insert tb select 'mary', 40, 'sz'  ,       'thanks' 
insert tb select 'kate' ,24, 'sd'  ,       'hi' 
insert tb select 'kate' ,45, 'uy'  ,       'sorry' 
insert tb select 'moss', 28, 'sh'  ,       'hello' 
insert tb select 'moss', 0, 'sz'   ,      'hi' 
go
select  * from tb a WHERE score = (SELECT MAX(score) FROM tb WHERE uname = a.uname) ORDER BY uname
go
drop table tb
go
/*
uname      score       location   remark
---------- ----------- ---------- ----------
kate       45          uy         sorry
mary       40          sz         thanks
moss       28          sh         hello*/

[mugua604]

LS不正确,如果有多个UNAME有相同的SCORE,数据出错!

[ws_hgo]

create table #tb(uname varchar(10), score int, location varchar(10) ,    remark varchar(10) )
insert #tb select 'mary',30,'sh','hello' 
insert #tb select 'mary',40,'sz','thanks' 
insert #tb select 'kate',24,'sd','hi' 
insert #tb select 'kate',45,'uy','sorry' 
insert #tb select 'moss',28,'sh','hello' 
insert #tb select 'moss',0,'sz','hi' 
go
select  * from #tb a where exists(select 1 from #tb where uname=a.uname and score<a.score)

uname      score       location   remark
---------- ----------- ---------- ----------
mary       40          sz         thanks
kate       45          uy         sorry
moss       28          sh         hello

(3 行受影响)

[ws_hgo]

人齐结贴