在一个表中,要删除冗余的记录。
delete from reader_cert where redr_cert_id in (select redr_cert_id from reader_cert group by redr_cert_id ,oper_date having oper_date=max(oper_date))
但日期会报错。
我要在重复的记录中删除掉日期不是最大的记录,既要保留一条最近的记录删除老记录。
redr_cert_id 中的记录有重复。
---------------------------------------------------------------
select redr_cert_id,max(oper_date) as oper_date into #t1 from reader_cert
group by redr_cert_id
select a.* into #t2 from reader_cert a,#t1 where a.oper_date = #t1.oper_date and #t1.redr_cert_id = a.redr_cert_id
delete from reader_cert
insert into reader_cert select * from #t2
drop table #t1
drop table #t2
-----------------------------------------------------------------------
update reader_cert set oper_date='1900-01-01' where oper_date is null
--因为oper_date 中有空值,所以也会被删掉的。故赋一个值
select max(redr_cert_id) as redr_cert_id ,cert_id,max(convert(datetime,oper_date)) as oper_date into #t1 from reader_cert
group by cert_id
select a.* into #t2 from reader_cert a,#t1 where a.cert_id = #t1.cert_id and #t1.redr_cert_id = a.redr_cert_id
delete from reader_cert
insert into reader_cert select * from #t2
drop table #t1
drop table #t2
跟据kaikaihe(开开) 的帮助,我修改了一下,终于实现了我的要求。谢谢!