資源描述：
《sql查詢和刪除重復(fù)字段的內(nèi)容》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫(kù)。
1、例如：id??????????name????????value1??????????????a????????????????pp2??????????????a????????????????pp3??????????????b????????????????iii4??????????????b????????????????pp5??????????????b????????????????pp6??????????????c????????????????pp7??????????????c????????????????pp8
2、??????????????c????????????????iiiid是主鍵要求得到這樣的結(jié)果id??????????name????????value1??????????????a????????????????pp3??????????????b????????????????iii4??????????????b????????????????pp6??????????????c????????????????pp8??????????????c????????????????iii方法1delete??YourTable??w
3、here??[id]??not??in??(select??max([id])??from??YourTable??group??by??(name??+??value))方法2delete??afrom??表??a??left??join(select??id=min(id)??from??表??group??by??name,value)b??on??a.id=b.idwhere??b.id??is??null查詢及刪除重復(fù)記錄的SQL語(yǔ)句查詢及刪除重復(fù)記錄的SQL語(yǔ)句1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleI
4、d）來(lái)判斷select*frompeoplewherepeopleIdin(select??peopleIdfrom??peoplegroupby??peopleIdhavingcount(peopleId)>1)2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來(lái)判斷，只留有rowid最小的記錄deletefrompeoplewherepeopleIdin(select??peopleIdfrompeoplegroupby??peopleId??havingcount(peopleId)>1)androwidnoti
5、n(selectmin(rowid)from??peoplegroupbypeopleIdhavingcount(peopleId)>1)3、查找表中多余的重復(fù)記錄（多個(gè)字段）select*fromvitaeawhere(a.peopleId,a.seq)in??(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)4、刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有rowid最小的記錄deletefromvitaeawhere(a.peopleId,a.seq)in??
6、(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)androwidnotin(selectmin(rowid)fromvitaegroupbypeopleId,seqhavingcount(*)>1)5、查找表中多余的重復(fù)記錄（多個(gè)字段），不包含rowid最小的記錄select*fromvitaeawhere(a.peopleId,a.seq)in??(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcou
7、nt(*)>1)androwidnotin(selectmin(rowid)fromvitaegroupbypeopleId,seqhavingcount(*)>1)(二)比方說(shuō)在A表中存在一個(gè)字段“name”，而且不同記錄之間的“name”值有可能會(huì)相同，現(xiàn)在就是需要查詢出在該表中的各記錄之間，“name”值存在重復(fù)的項(xiàng)；SelectName,Count(*)FromAGroupByNameHavingCount(*)>1如果還查性別也相同大則如下:SelectName,sex,Count(*)FromAGroupByName,sexH
8、avingCount(*)>1(三)方法一declare@maxinteger,@idintegerdeclarecur_rowscursorlocalforselect主字段,count(*
