有很多種方法都可以實(shí)現(xiàn)sql server刪除數(shù)據(jù)，下面就為您介紹了其中5種最常見的方法，如果您對sql server刪除數(shù)據(jù)方面感興趣的話，不妨一看。

1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來判斷
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來判斷，只留有rowid最小的記錄
delete from people
where 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)

3、查找表中多余的重復(fù)記錄（多個(gè)字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、sql server刪除數(shù)據(jù)--刪除表中多余的重復(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)

5、查找表中多余的重復(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)
 

【編輯推薦】

sql server刪除有默認(rèn)值的列的方法

SQL SERVER連接失敗的解決方案

帶您了解SQL Server游標(biāo)

八步輕松搞定SQL Server自動(dòng)備份

SQL Server的優(yōu)點(diǎn)和缺點(diǎn)