如何查出这样的记录？

[netying]

这样一个表：

ID name 成绩
-----------------
1 张三 90
2 张三 80
3 李四 80
4 李四 65
5 李四 20
6 张三 30
7 李四 90
8 李四 50

---------------------

不断的有成绩记录添加进来，ID自动增长。

如何得到每个人的最新一条记录？比如上边，我需要得到：


6 张三 30
8 李四 50

谢谢！

[carpenter01]

select * from table where id in(select max(id) from table group by name)

[天-笑]

declare @t table ( ID int , name varchar(8),cj float)
insert into @t
select 1,'张三',90 union all
select 2,'张三',90 union all
select 3,'李四',80 union all
select 4,'李四',65 union all
select 5,'李四',20 union all
select 6,'张三',30 union all
select 7,'李四',90 union all
select 8,'李四',50 

select name,max(ID),replace(max(convert(char(8),id)+convert(varchar(8),cj)),
							max(convert(char(8),id)),'')

from @t
group by name
---------------------------

(8 行受影响)
name                 
-------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
李四       8                  50
张三       6                  30

(2 行受影响)

[--小F--]

select * from tb t where not exists(select 1 from tb where name=t.name and id>t.id)

[天-笑]

--楼主还可以这样 
declare @t table ( ID int , name varchar(8),cj float)
insert into @t
select 1,'张三',90 union all
select 2,'张三',90 union all
select 3,'李四',80 union all
select 4,'李四',65 union all
select 5,'李四',20 union all
select 6,'张三',30 union all
select 7,'李四',90 union all
select 8,'李四',50 

;with t as (

select row_number() over ( partition by name order by id desc) as Row ,* from @t 
)

select ID,name,cj from t where Row = 1


/×

(8 行受影响)
ID          name     cj
----------- -------- ----------------------
8           李四       50
6           张三       30

(2 行受影响)
×/

[天-笑]

--楼主还可以这样 

declare @t table ( ID int , name varchar(8),cj float)
insert into @t
select 1,'张三',90 union all
select 2,'张三',90 union all
select 3,'李四',80 union all
select 4,'李四',65 union all
select 5,'李四',20 union all
select 6,'张三',30 union all
select 7,'李四',90 union all
select 8,'李四',50 

select * from @t t where not exists(select 1 from @t where name=t.name and id>t.id)

/×

(8 行受影响)
ID          name     cj
----------- -------- ----------------------
6           张三       30
8           李四       50

(2 行受影响)

×/

[开着拖拉机泡妞]

--SQL Server 2000
select * fromk tb a
where not exists(select 1 from tb b where a.name =b.name and a.id<b.id)
或者
select * from tb a
where a.id=(select max(id) from tb b where a.name=b.name)

--SQL Server2005以上版本
;with t
as
(
select px=row_number()over(partition by name order by id desc),
* from tb
)
select id,name,cj from t where px=1