柚子快報(bào)邀請(qǐng)碼778899分享：sql 查詢重復(fù)數(shù)據(jù)及刪除

http://yzkb.51969.com/

查找所有重復(fù)標(biāo)題的記錄： SELECT * FROM t_info a WHERE ((SELECT COUNT()FROM t_infoWHERE Title = a.Title) > 1)ORDER BY Title DESC 一。查找重復(fù)記錄1。 查找全部重復(fù)記錄 Select * From 表 Where 重復(fù)字段 In (Select 重復(fù)字段 From 表 Group By 重復(fù)字段 Having Count()>1) 2。過濾重復(fù)記錄(只顯示一條) Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title) 注：此處顯示ID最大一條記錄 二。刪除重復(fù)記錄 1。刪除全部重復(fù)記錄（慎用） Delete 表 Where 重復(fù)字段 In (Select 重復(fù)字段 From 表 Group By 重復(fù)字段 Having Count()>1) 2。保留一條（這個(gè)應(yīng)該是大多數(shù)人所需要的 _） Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title) 注：此處保留ID最大一條記錄1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來判斷select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 3、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來判斷，只留有rowid最小的記錄 delete from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) 4、查找表中多余的重復(fù)記錄（多個(gè)字段） select * from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count() > 1) 5、刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有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) 6、查找表中多余的重復(fù)記錄（多個(gè)字段），不包含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)

實(shí)際應(yīng)用到的： select * from 表A where A.字段 in (select AAC147 from 表A group by A.字段 having count(A.字段) > 1)

Delete 表B Where ID Not In (Select Max(ID) From 表B Group By 字段)

柚子快報(bào)邀請(qǐng)碼778899分享：sql 查詢重復(fù)數(shù)據(jù)及刪除

http://yzkb.51969.com/

精彩鏈接