切换到窄版

Good技术分享

 找回密码
 立即注册
搜索
热搜: 活动 交友
查看: 1884|回复: 0

SQL语句实现删除重复记录并只保留一条

[复制链接]
  • TA的每日心情
    无聊
    2024-3-22 17:23
  • 83

    主题

    108

    帖子

    19999万

    积分

    管理员

    Rank: 9Rank: 9Rank: 9

    积分
    199991752

    最佳新人活跃会员热心会员推广达人宣传达人灌水之王突出贡献优秀版主荣誉管理论坛元老

    发表于 2023-6-7 17:25:03 | 显示全部楼层 |阅读模式
    这篇文章主要介绍了SQL语句实现删除重复记录并只保留一条,本文直接给出实现代码,并给出多种查询重复记录的方法,需要的朋友可以参考下
    SQL:删除重复数据,只保留一条用SQL语句,删除掉重复项只保留一条在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
    1. delete WeiBoTopics where Id in(select max(Id) from WeiBoTopics group by WeiBoId,Title having COUNT(*) > 1);
    复制代码
    1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
    1. select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
    复制代码
    2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
    1. delete from people where   peopleName in (select peopleName    from people group by peopleName      having count(peopleName) > 1) and   peopleId not in (select min(peopleId) from people group by peopleName     having count(peopleName)>1)
    复制代码
    3、查找表中多余的重复记录(多个字段)
    1. select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
    复制代码
    4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
    1. delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
    复制代码
    5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
    1. select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
    复制代码
    6.消除一个字段的左边的第一位:
    1. update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like \'村%\'
    复制代码
    7.消除一个字段的右边的第一位:
    1. update tableName set [Title]=left([Title],(len([Title])-1)) where Title like \'%村\'
    复制代码
    8.假删除表中多余的重复记录(多个字段),不包含rowid最小的记录
    1. update vitae set ispass=-1 where peopleId in (select peopleId from vitae group by peopleId,seq having count(*) > 1) and seq in (select seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
    复制代码



    来源: SQL语句实现删除重复记录并只保留一条
    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则


    QQ|手机版|小黑屋|Good技术分享

    GMT+8, 2024-4-19 23:59 .

    www.52yfw.com

    Technology sharing

    快速回复 返回顶部 返回列表