summertie
2017-02-17 04:11
采纳率: 100%
浏览 1.4k

sql如何删除表中重复的数据

表 Sys_WFApproveLog_bak ,表中四个字段(logid,ver,wa,id) , 有三条记录重复,值都相同, 只有ver字段不同。 需要 删除其中的两条,只保留一条数据。如何实现,谢谢了。
logid ver wa id

7933 1 2 1
7933 2 2 1
7933 3 2 1

select LogID ,VER, *
from Sys_WFApproveLog_bak
where LogID in (select LogID from Sys_WFApproveLog_bak group by LogID having count(1) >= 2) and
logid=7933

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

6条回答 默认 最新

  • Cicyer2513 2017-02-17 06:03
    已采纳

    过滤出全部重复
    select * from a as a1 where exists(select 1 from a as a2 where a1.logid=a2.logid and a1.ver <>a2.ver )

    接着删除逻辑就自己做吧,没有特别要求的话就这样

    DELETE FROM A WHERE ver not in( select max(ver) from a as a1 where exists(select 1 from a as a2 where a1.logid=a2.logid and a1.ver <>a2.ver )
    and
    logid in ( select max(ver) from a as a1 where exists(select 1 from a as a2 where a1.logid=a2.logid and a1.ver <>a2.ver )

    打赏 评论
  • jiacye13 2017-02-17 05:01

    delete from a where logid='1' and ver != 'm';

    打赏 评论
  • 你可以教我C++么 2017-02-17 05:13

    兄弟 你的描述能不能再清楚些??

    打赏 评论
  • lihongbiao0610 2017-02-17 05:54

    delete from Sys_WFApproveLog_bak
    where logid,wa,id,ver not in (
    select logid,wa,id,max(ver) ver from Sys_WFApproveLog_bak group by logid,wa,id
    )

    打赏 评论
  • liyu1059915776 2017-02-17 07:40

    我也看了半天没看懂,你这是要干啥?

    打赏 评论
  • 兔子托尼啊 2017-02-17 07:50
    打赏 评论

相关推荐 更多相似问题