SQL 查询表格中所有记录的最新记录:
http://topic.csdn.net/u/20071027/12/ab7278f9-8dd0-4c37-9b39-25564613080f.html
1.
select a.* from table1 a
left join
(select name,max(time)as time from table1 group by name)b
on a.name=b.name and a.time=b.time
2.
select a.* from table1 a
where not exists(select 1 from table1 where a.name=name and time<a.time)
SQL 按多个字段内容查询最新记录
http://topic.csdn.net/u/20090403/02/c7540c96-6498-4f4b-9c09-6ecfde28f7bd.html
1.
declare @t table(id int, name varchar(10), Time1 datetime, Time2 datetime)
insert @t select 1,’AA’,’2008-5-10′,’2009-1-1′
insert @t select 2,’AA’,’2008-11-5′,null
insert @t select 3,’BB’,’2008-5-10′,’2009-2-14′
insert @t select 4,’BB’,’2008-8-23′,’2009-1-5′
insert @t select 5,’BB’,’2008-9-14′,’2009-4-1′
insert @t select 6,’CC’,’2009-2-22′,null
insert @t select 7,’CC’,’2008-10-10′,’2009-1-10′
select * from @t t where not exists(
))
select * from @t t where not exists(select 1 from @t where name=t.name and (time1>t.time1 or time2>t.time2))
用not exists会高效点,特别是数据量大的时候.
2.
大体上看了下楼主的问题，没有细读各位回帖，我觉得，首先应该将（逻辑）思路搞清楚，那么，剩下的就仅仅是sql的堆砌了。
ok，根据楼主的意思，我个人的意见描述如下：
1.将每条记录的time1与time2字段比较，取出较大值，作为一个新的虚拟列：
select case when time1>time2 then time1 else time2 newtime from table
2.然后，对上面sql生成的“视图”再按照name分组取最大的newtime值即可
参考sql：
select name,max(newtime)
from
(
select case when time1>time2 then time1 else time2 newtime from table
)