sql 查询

初始化脚本

 

     CREATE TABLE [temp] (
     [Rid] [int]  NULL,
     [content] [nvarchar]  (50) NULL,
     [time] [datetime]  NULL DEFAULT (getdate()))

     INSERT [temp] ([Rid],[content],[time]) VALUES ( 1,'开机','2009/1/14 14:53:49')
     INSERT [temp] ([Rid],[content],[time]) VALUES ( 1,'关机','2009/1/14 14:53:49')
     INSERT [temp] ([Rid],[content],[time]) VALUES ( 1,'锁定','2009/1/14 14:53:54')
     INSERT [temp] ([Rid],[content],[time]) VALUES ( 2,'重启','2009/1/14 14:53:55')
     INSERT [temp] ([Rid],[content],[time]) VALUES ( 2,'注销','2009/1/14 14:53:54')
     INSERT [temp] ([Rid],[content],[time]) VALUES ( 3,'解锁','2009/1/14 14:53:54')

 

表内容如下：

环境：sql server 2005
要求： 查出每个Rid插入的最后记录
结果如下：
1                 fkj                   2009/1/14 14:53:54
2                 ;po'                 2009/1/14 14:53:55
3                erete                2009/1/14 14:53:54
请问sql语句如何写？

 

解决办法如下：

select temp1.*
FROM Temp as temp1,
(select rid,MAX(time) as time
FROM temp
group by rid
) as temp2
where temp2.rid = temp1.rid
and temp2.time = temp1.time
order by rid