我有一张表设计成这样id updatetimea 2011-4-11
a 2011-4-12
b 2011-4-1
b 2011-4-7
c 2011-3-1
我需要查出每一个id的最早时间的一条记录 结果如下id updatetime
a 2011-4-11
b 2011-4-1
c 2011-3-1
请问各位如何写sql?
a 2011-4-12
b 2011-4-1
b 2011-4-7
c 2011-3-1
我需要查出每一个id的最早时间的一条记录 结果如下id updatetime
a 2011-4-11
b 2011-4-1
c 2011-3-1
请问各位如何写sql?
select a.* from tb a where not exists (select 1 from tb where id=a.id and updatetime<a.updatetime)
declare @t table
(
id int not null,
updatetime date not null
)
insert into @t values
(1,'2001-12-21'),
(1,'2001-11-21'),
(2,'2001-1-2'),
(2,'2001-1-12'),
(3,'2001-2-4')select id, max(updatetime) as updatetime
from @t
*
from
tb t
where
updatetime=(select min(updatetime) from tb where id=a.id )
select id,min(updatetime) from tb group by idselect a.* from tb a where not exists (select 1 from tb where id=a.id and updatetime<a.updatetime)select * from tb t where updatetime=(select min(updatetime) from tb where id=a.id )都可以