当前位置: 主页>网站教程>建站知识> 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人阅读,期待你的评论!发表评论
昵称: 网址: 验证码: 点击我更换图片
最新评论

本文标签

广告赞助

订阅获得更多模板