求一sql语句

[xjb_netboy]

表的定义

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLE1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLE1]
GO

CREATE TABLE [dbo].[TABLE1] (
[id] [int] NULL , --id号
[name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , --姓名
[cid] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , --单位id
[time] [datetime] NULL --数据创建时间
) ON [PRIMARY]
GO


表数据

ID NAME CID TIME
-----------------------------------------------------------------
1 xxx 1 2005-1-1
2 ffff 1 2005-8-31
3 ffff 1 2005-7-1
4 ffff 2 2005-7-1
5 dddd 3 2005-7-1



现在问题是，要根据cid选择中每个人最新的一条纪录
处理流程就是

以上数据要求sql得到结果

ID NAME CID TIME
-----------------------------------------------------------------
1 xxx 1 2005-1-1
2 ffff 1 2005-8-31


我只能写出这样的sql
SELECT id, name, cid, [time] FROM TABLE1 WHERE (cid = '1') ORDER BY [time] DESC

得到结果是

ID NAME CID TIME
-----------------------------------------------------------------
2 ffff 1 2005-8-31
3 ffff 1 2005-7-1
1 xxx 1 2005-1-1


请高手相助

[子陌红尘]

select
a.*
from
TABLE1 a
where
a.ID = (select top 1 ID from TABLE1 where NAME=a.NAME order by TIME desc)

[子陌红尘]

select
a.*
from
TABLE1 a
where
a.ID = (select top 1 ID from TABLE1 where NAME=a.NAME order by [TIME] desc)
and
CID = 1

[vivianfdlpw]

select * from table1 t
where not exists(select 1 from table1 where name=t.name and [time]>t.[time])

[vivianfdlpw]

select * from table1 t
where not exists(select 1 from table1 where name=t.name and [time]>t.[time])
and
cid=1

[xjb_netboy]

请问
select
a.*
from
TABLE1 a
where
a.ID = (select top 1 ID from TABLE1 where NAME=a.NAME order by TIME desc)

语句中的a是什么意思?

[turenjie]

楼主,a 为表的别名!

[xjb_netboy]

谢谢你们的sql语句，很管用，但是，象这a,t啦，这些是什么东西，为什么它是一个表名，不耻下问，请高手讲解?

[alexsongff]

SELECT id, name, cid, [time] FROM TABLE1 WHERE (cid = '1') group by Name ORDER BY [time] DESC