SQL Server 刪除重復記錄的SQL語句

比如現在有一人員表 （表名：peosons）
若想將姓名、身份證號、住址這三個字段完全相同的記錄查詢出來
select p1.* from persons p1,persons p2 where p1.id<>p2.id and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address

可以實現上述效果．

幾個刪除重復記錄的SQL語句

1.用rowid方法

2.用group by方法

3.用distinct方法

1。用rowid方法

據據oracle帶的rowid屬性，進行判斷，是否存在重復,語句如下：

查數據:

select * from table1 a where rowid !=(select max(rowid)
from table1 b where a.name1=b.name1 and a.name2=b.name2......)

刪數據：

delete from table1 a where rowid !=(select max(rowid)
from table1 b where a.name1=b.name1 and a.name2=b.name2......)

2.group by方法

查數據:

　　select count(num), max(name) from student --列出重復的記錄數，并列出他的name屬性
　　group by num
　　having count(num) >1 --按num分組后找出表中num列重復，即出現次數大于一次

刪數據：

　　delete from student
　　group by num
　　having count(num) >1
　　這樣的話就把所有重復的都刪除了。

3.用distinct方法 -對于小的表比較有用

create table table_new as select distinct * from table1 minux
truncate table table1;
insert into table1 select * from table_new;

查詢及刪除重復記錄的方法大全

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 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、查找表中多余的重復記錄（多個字段）
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)