sql删除重复数据

2014-8-5 liyangweb.com 李杨 MySQL

用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)  

 

标签: SQL mysql

评论:

atollbreak
2019-01-01 19:22
<a href="http://www.bodhiwords.com/nike-hypervenom-gold-herren-schuhed">nike hypervenom gold herren</a> <a href="http://www.celeblesbians.com/hommes-asics-gel-noosa-tri-10-violet-rose-obuvf">hommes asics gel noosa tri 10 violet rose</a> <a href="http://www.darycheh.com/adidas-springblade-drive-rojo-corriendo-zapatos-guide-butya">adidas springblade drive rojo corriendo zapatos guide</a> <a href="http://www.domainvlogs.com/dark-r%C3%B8d-nike-cortez-butya">dark r酶d nike cortez</a> <a href="http://www.ercots.com/adidas-superstar-kvinners-salg-paper-obuvd">adidas superstar kvinners salg paper</a> <a href="http://www.frgans.com/maschio-asics-gel-lyte-5-rosso-argento-scarpec">maschio asics gel lyte 5 rosso argento</a>
atollbreak http://www.atollbreak.com/
ihavewed
2018-12-29 09:20
<a href="http://www.myfraimz.com/michael-crabtree-15-jersey-xl-nfle">michael crabtree 15 jersey xl</a> <a href="http://www.winnercells.com/air-max-360-plus-runningf">air max 360 plus</a> <a href="http://www.atppest.com/minnesota-timberwolves-21-kevin-garnett-black-jerseys-shorts-nba-suits-nflv">minnesota timberwolves 21 kevin garnett black jerseys shorts nba suits</a> <a href="http://www.windriftbar.com/nike-roshe-camouflage-rosha-run-trainersa">nike roshe camouflage rosha run</a> <a href="http://www.kidtokidboise.com/nike-air-max-2016-p%C3%BArpura-jack-scarped">nike air max 2016 p煤rpura jack</a> <a href="http://www.mesmertronic.com/asics-asics-gel-kayano-17-oro-marrone-kurpesd">asics asics gel kayano 17 oro marrone</a>
ihavewed http://www.ihavewed.com/
scmvietnam
2018-12-29 04:41
<a href="http://www.getfancastapp.com/air-jordan-10-schwarz-blau-obuve">air jordan 10 schwarz blau</a> <a href="http://www.lottopurchase.com/adidas-neo-canvas-blanco-marr%C3%B3n-kurpesg">adidas neo canvas blanco marr贸n</a> <a href="http://www.tejarateparsi.com/nike-lunarglide-6-kvinners-l%C3%B8ping-sko-obuvc">nike lunarglide 6 kvinners l酶ping sko</a> <a href="http://www.vaccinebuynet.com/nike-cortez-ni%C3%B1os-negro-amarillo-butya">nike cortez ni帽os negro amarillo</a> <a href="http://www.akivla.com/mens-nike-los-angeles-rams-13-kurt-warner-limited-navy-blue-player-name-number-tank-top-nfl-jersey-nflu">mens nike los angeles rams 13 kurt warner limited navy blue player name number tank top nfl jersey</a> <a href="http://www.kleenexformen.com/nike-air-max-2017-kpu-ii-verde-gituttio-kurpesb">nike air max 2017 kpu ii verde gituttio</a>
[url=http://www.scmvietnam.com/]scmvietnam[/url]
ksmartcity
2018-12-27 15:19
<a href="http://www.offgridac.com/nike-air-presto-skull-schwarz-gelb-chaussuresf">nike air presto skull schwarz gelb</a> <a href="http://www.whylux.com/nike-free-run-4.0-rosa-hvit-scarpee">nike free run 4.0 rosa hvit</a> <a href="http://www.aouladmhand.com/nike-limited-justin-coleman-jersey-28-nfl-seattle-seahawks-rush-realtree-mens-camo-nfls">nike limited justin coleman jersey 28 nfl seattle seahawks rush realtree mens camo</a> <a href="http://www.bukafalar.com/polo-ralph-lauren-dam%C3%A4nner-big-pony-polo-rosa-polot">polo ralph lauren dam盲nner big pony polo rosa</a> <a href="http://www.drgnatarajan.com/nfl-austin-blythe-ash-66-los-angeles-rams-nike-backer-t-shirt-nflz">nfl austin blythe ash 66 los angeles rams nike backer t shirt</a> <a href="http://www.ideasevoke.com/nike-air-max-97-fl%C3%A5den-ship-kurpesd">nike air max 97 fl氓den ship</a>
ksmartcity http://www.ksmartcity.com/

发表评论:

Powered by emlog 冀ICP备13011830号-1