有记录如下
id score time
1 2 2006-11-10
1 3 2006-11-11
1 1 2007-11-1
2 2 2006-11-9
2 2 2007-5-6我想返回的结果是
id score time
1 1 2007-11-1
2 2 2007-5-6就是返回不同id号下最新日期的TOP 1 数据.请教SQL语句应该怎么写才好?
id score time
1 2 2006-11-10
1 3 2006-11-11
1 1 2007-11-1
2 2 2006-11-9
2 2 2007-5-6我想返回的结果是
id score time
1 1 2007-11-1
2 2 2007-5-6就是返回不同id号下最新日期的TOP 1 数据.请教SQL语句应该怎么写才好?
select * from TAB a where time=(select min(time) from TAB where id=a.id)
select ID from bbb group by Id
) and Time in (select Max(Time) from Table group by Id)
试试这样能不能解决问题
select 1,2,'2006-11-10' union all
select 1,3,'2006-11-11' union all
select 1,1, '2007-11-1' union all
select 2,2, '2006-11-9' union all
select 2,2,'2007-5-6'select a.[id],a.[score],a.[time] from #table a
inner join (select max([time]) as [time] from #table group by [id]) b
on a.[time]=b.[time]
----------- ----------- ------------------------------------------------------
1 1 2007-11-01 00:00:00.000
2 2 2007-05-06 00:00:00.000(所影响的行数为 2 行)
select 1,2,'2006-11-10' union all
select 1,3,'2006-11-11' union all
select 1,1,'2007-11-1' union all
select 2,2,'2006-11-9' union all
select 2,2,'2007-5-6'--下面显示最终结果
select a.[id],a.[score],a.[time] from #table a
inner join (select [id],max([time]) as [time] from #table group by [id]) b
on a.[id]=b.[id] and a.[time]=b.[time]
order by a.[id]
select * from #table a where time=(select max(time) from #table where id=a.id) order by a.id--2:
select * from #table a where (select count(*) from #table where id=a.id and time >a.time)<1
select * from tablename a where time =(select min(time) from tablename where id=a.id)