当前位置: 主页>网站教程>建站知识> mysql删除重复数据只保留一条 查询和删除表中重复数据sql语句
分享本文到:

mysql删除重复数据只保留一条 查询和删除表中重复数据sql语句

发布时间:01/15 来源:mysql删除 浏览: 关键词: 查询和删除表中重复数据sql语句 mysql删除重复数据只保留一条 mysql删除重复
使用SQL删除表中重复数据(单字段): 
1.查询重复数据:
SELECT
    *
FROM
    info_1688
WHERE
    storeURL IN (
        SELECT
            storeURL
        FROM
            info_1688
        GROUP BY
            storeURL
        HAVING
            COUNT(storeURL) > 1
    )
 
2.删除重复数据:
DELETE
FROM
    info_1688
WHERE
    storeURL IN (
 
        SELECT
            storeURL
        FROM
            info_1688
        GROUP BY
            storeURL
        HAVING
            COUNT(id) > 1
 
    )
AND id NOT IN (
 
    SELECT
        MIN(id) AS id 
    FROM
        info_1688
    GROUP BY
        storeURL
    HAVING
        COUNT(id) > 1
 
)
 
Mysql异常:you can’t specify target table ‘info_1688’ for update in FROM clause 
解决方法:select的结果再通过一个中间表select,可以避免此错误
 
DELETE
FROM
    info_1688
WHERE
    storeURL IN (
    SELECT storeURL FROM (
        SELECT
            storeURL
        FROM
            info_1688
        GROUP BY
            storeURL
        HAVING
            COUNT(id) > 1
    ) AS tab1
    )
AND id NOT IN (
    SELECT id FROM (
        SELECT
        MIN(id) AS id 
        FROM
        info_1688
        GROUP BY
        storeURL
        HAVING
        COUNT(id) > 1
    ) AS tab2
)
 
正常可以使用的就是这2条语句,KUCMS去除代码
 
查重
select * from v3_vod 
where name in (select name from v3_vod group by name having count(name) > 1) 
 
删除重复 保留最旧的一条
 
DELETE
FROM
    v3_vod
WHERE
    name IN (
    SELECT name FROM (
        SELECT
            name
        FROM
            v3_vod
        GROUP BY
            name
        HAVING
            COUNT(id) > 1
    ) AS tab1
    )
AND id NOT IN (
    SELECT id FROM (
        SELECT
        MIN(id) AS id 
        FROM
        v3_vod
        GROUP BY
        name
        HAVING
        COUNT(id) > 1
    ) AS tab2
)
 
优化代码后是
delete from V3_VOD
where NAME in ( select NAME from (
        select NAME from V3_VOD group by NAME having count(ID) > 1
    ) as TAB1 )
and ID not in (
    select ID from (
        select min(ID) as ID  from V3_VOD group by NAME having count(ID) > 1
    ) as TAB2
)
 
 
 
 
SQL删除重复数据只保留一条
用SQL语句,删除掉重复项只保留一条
在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 
select * from people 
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 
 
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录 
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、查找表中多余的重复记录(多个字段) 
select * from vitae a 
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 
 
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录 
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最小的记录 
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.消除一个字段的左边的第一位:
 
update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'
 
7.消除一个字段的右边的第一位:
 
update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'
 
8.假删除表中多余的重复记录(多个字段),不包含rowid最小的记录 
update vitae set ispass=-1
where peopleId in (select peopleId from vitae group by peopleId
 
责任编辑:mysql删除

当前文章网址:http://www.2t2c.com/jiaocheng/yunying/201801/mysqldelete.html 转载请注明出处!

相关文章

共有79人阅读,期待你的评论!发表评论
昵称: 网址: 验证码: 点击我更换图片
最新评论

本文标签

广告赞助

订阅获得更多模板