SQL Server 删除重复记录的SQL语句

概述比如现在有一人员表 （表名：peosons） 若想将姓名、身份z号、住址这三个字段完全相同的记录查询出来 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 可以实现上述效果． 几个删除重复记录的S… 比如现在有一人员表 （表名：peosons）
若想将姓名、身份z号、住址这三个字段完全相同的记录查询出来
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,seq having count(*) > 1)
and rowID not in (select min(rowID) from vitae group by peopleID,seq having count(*)>1)
总结

以上是内存溢出为你收集整理的SQL Server 删除重复记录的SQL语句全部内容，希望文章能够帮你解决SQL Server 删除重复记录的SQL语句所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。