如何删除重复的记录?

fanpingli 2002-04-28 11:10:34
在一个表中,要删除冗余的记录。
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 中的记录有重复。
...全文
32 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
playmud 2002-04-28
  • 打赏
  • 举报
回复
SELECT 各个字段, MAX(oper_date) AS oper_date
FROM reader_cert
GROUP BY 重复项字段
playmud 2002-04-28
  • 打赏
  • 举报
回复
不对
playmud 2002-04-28
  • 打赏
  • 举报
回复
delete from reader_cert where redr_cert_id in (select redr_cert_id,max(oper_date) as oper_date from reader_cert group by redr_cert_id ,oper_date )
kaikaihe 2002-04-28
  • 打赏
  • 举报
回复
sorry!!
应该
select redr_cert_id,max(oper_date) as oper_date into #t1 from reader_cert
group by redr_cert_id
fanpingli 2002-04-28
  • 打赏
  • 举报
回复
服务器: 消息 8155,级别 16,状态 1,行 1
没有为第 2 列(属于 '#t1')指定列。
kaikaihe 2002-04-28
  • 打赏
  • 举报
回复
select redr_cert_id,max(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
fanpingli 2002-04-28
  • 打赏
  • 举报
回复
又修改了一下
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
fanpingli 2002-04-28
  • 打赏
  • 举报
回复
update reader_cert set oper_date='1900' where oper_date is null

select max(redr_cert_id) as redr_cert_id ,cert_id,max(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(开开) 的帮助,我修改了一下,终于实现了我的要求。谢谢!

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧