delete from table1 a
from not exists(
select * from (select name,min(time) time from table1 group by table1 ) b
where a.name=name and a.time=time)
from not exists(
select * from (select name,min(time) time from table1 group by table1 ) b
where a.name=name and a.time=time)
select '张三','01099100010401102','2006-2-14 7:50:00' union all
select '车俊杰','01099100010401101','2006-2-14 9:00:00' union all
select '张三','01099100010401102','2006-2-14 11:00:00' union all
select '车俊杰','01099100010401101','2006-2-14 13:00:00'select * from #tdelete #t
from #t A
where exists (select * from #t B where A.id=B.id and A.time >B.time)select * from #tdrop table #t
insert @
select '张三','01099100010401102','2006-2-14 7:50:00'
union select
'车俊杰','01099100010401101','2006-2-14 9:00:00'
union select
'张三','01099100010401102','2006-2-14 11:00:00'
union select
'车俊杰','01099100010401101','2006-2-14 13:00:00'delete @
from (select name,min(time) time from @ group by name ) b,@ a
where a.name=b.name and a.time=b.time select * from @Name ID time
-------------------------------------------------- -------------------------------------------------- ------------------------------------------------------
车俊杰 01099100010401101 2006-02-14 13:00:00.000
张三 01099100010401102 2006-02-14 11:00:00.000
insert @a
select '张三','01099100010401102', '2006-2-14 7:50:00' union all
select '车俊杰','01099100010401101', '2006-2-14 9:00:00' union all
select '张三', '01099100010401102', '2006-2-14 11:00:00' union all
select '车俊杰','01099100010401101', '2006-2-14 13:00:00'select * from @a a
where not exists(select * from @a where ID=a.id and time1<a.time1)结果:
Name1 ID Time1
张三 01099100010401102 2006-02-14 07:50:00.000
车俊杰 01099100010401101 2006-02-14 09:00:00.000
a.*
from
表 a
where
datediff(dd,a.time,'2006-02-14')=0
and
not exists(select 1 from 表 where datediff(dd,time,a.time)=0 and time<a.time)
a.*
from
表 a
where
datediff(dd,a.time,'2006-02-14')=0
and
not exists(select 1 from 表 where name=a.name datediff(dd,time,a.time)=0 and time<a.time)
insert @t select '张三 ','01099100010401102','2006-2-12 07:50:00'
union select '车俊杰','01099100010401101','2006-2-12 09:00:00'
union select '张三 ','01099100010401102','2006-2-14 07:50:00'
union select '车俊杰','01099100010401101','2006-2-14 09:00:00'
union select '张三 ','01099100010401102','2006-2-14 11:00:00'
union select '车俊杰','01099100010401101','2006-2-14 13:00:00'declare @time datetime
set @time='2006-02-14'delete a from @t a
where
datediff(dd,a.time,@time)!=0
or
(datediff(dd,a.time,@time)=0
and
exists(select 1 from @t where name=a.name and datediff(dd,time,a.time)=0 and time<a.time))select * from @t
where
datediff(dd,A.时间,'2006-02-14')=0
and not exists (select 1 from @ where ID=A.ID and time<A.time)
--or
select * from 表 A
where
datediff(dd,A.时间,'2006-02-14')=0
and not exists (select 1 from 表 where Name=A.Name and time<A.time)
--or
select A.* from 表 A
where
datediff(dd,A.时间,'2006-02-14')=0
and time=(select min(time) from 表 where ID=A.ID)
--or
select A.* from 表 A
where
datediff(dd,A.时间,'2006-02-14')=0
and time=(select min(time) from 表 where Name=A.Name )
a.*
from
表 a
where
datediff(dd,a.time,'2006-02-14')=0
and
not exists(select 1 from 表 where id = a.id and datediff(dd,time,a.time)=0 and time<a.time)