这里的ID相当于你的人的字段. 大致为: --search select * from tb t where 日期时间 = (select max(日期时间) from tb where id = t.id and datediff(day,日期时间,t.日期时间) = 0)select * from tb t where not exists (select 1 from tb where id = t.id and datediff(day,日期时间,t.日期时间) = 0 and 日期时间 > t.日期时间)--删除 delete tb from tb t where 日期时间 not in (select max(日期时间) from tb where id = t.id and datediff(day,日期时间,t.日期时间) = 0)delete tb from tb t where 日期时间 not exists (select 1 from tb where id = t.id and datediff(day,日期时间,t.日期时间) = 0 and 日期时间 > t.日期时间)
SELECT * FROM TB T WHERE COLLECTLOGLD= (SELECT MAX(COLLECTLOGLD) FROM TB WHERE DATE=T.DATE AND TIME=T.TIME AND CARDCODE=T.CARDCODE)
delete tb from tb t where date not exists (select 1 from tb where id = t.id and time=T.time and date > t.date )
你的cardcode , date , time存在相同的?怎么搞?用另外两个字段? 大致写法为:select * from tb t where collectlogid = (select max(collectlogid) from tb where cardcode = t.cardcode and date = t.date and time = time) select * from tb t where not exists (select 1 from tb where cardcode = t.cardcode and date = t.date and time = time and collectlogid > t.collectlogid)
符合什么条件,才算是数据重复 delete from tb t where id! = (select top 1 id from tb where id = t.id and datediff(day,日期时间,t.日期时间) = 0)
大致为:
--search
select * from tb t where 日期时间 = (select max(日期时间) from tb where id = t.id and datediff(day,日期时间,t.日期时间) = 0)select * from tb t where not exists (select 1 from tb where id = t.id and datediff(day,日期时间,t.日期时间) = 0 and 日期时间 > t.日期时间)--删除
delete tb from tb t where 日期时间 not in (select max(日期时间) from tb where id = t.id and datediff(day,日期时间,t.日期时间) = 0)delete tb from tb t where 日期时间 not exists (select 1 from tb where id = t.id and datediff(day,日期时间,t.日期时间) = 0 and 日期时间 > t.日期时间)
(SELECT MAX(COLLECTLOGLD) FROM TB WHERE
DATE=T.DATE AND TIME=T.TIME AND CARDCODE=T.CARDCODE)
大致写法为:select * from tb t where collectlogid = (select max(collectlogid) from tb where cardcode = t.cardcode and date = t.date and time = time) select * from tb t where not exists (select 1 from tb where cardcode = t.cardcode and date = t.date and time = time and collectlogid > t.collectlogid)
delete from tb t where id! = (select top 1 id from tb where id = t.id and datediff(day,日期时间,t.日期时间) = 0)